CODEWITHSUNDEEP
python
Raptor776
Raptor776

Reputation: 190

How to write data to excel using python for stock data being pulled from yahoo

I have the following code which helps me to pull daily data for a number of stocks I have stored in a worksheet. What I was hoping to accomplish was to have the daily data returned and stored in another worksheet.

I am struggling to write a code which accomplishes this task. Currently I am able to pull the data for each of the individual stocks, though I have no way of storing this information. Any help will be appreciated. For the sake of testing I only tried to store Open and Close, ideally I would like all the parameters from yahoo finance to be stored.

import numpy as np
import pandas as pd
import xlsxwriter


df=pd.read_csv('Stock Companies Modified.csv', sep=',',header=True)
df.columns = ['StockSymbol', 'CompanyName', 'ClosingPrice', 'MarketCap', 'IPOYear', 'Sector', 'Industry']

workbook = xlsxwriter.Workbook('New Workbook.xlsx')
worksheet = workbook.add_worksheet()

df = df.convert_objects(convert_numeric=True)
df.dtypes

from pandas.io.data import DataReader
from datetime import datetime
for x in df.StockSymbol:
    if len(x)<=4:
        ClosingPrice = DataReader(x, 'yahoo', datetime(2015,1,1), datetime(2015,7,1))

        row = 0
        col = 0
        #This is the area where I am getting an error, and to be honest I dont know how to do it correctly

        for Open, Close in (ClosingPrice):
            worksheet.write(row, col, (ClosingPrice['Open']))
            worksheet.write(row,col+1,(ClosingPrice['Close']))
            row+=1

            workbook.close()

        print x
    else:
        print("This is not working")

Upvotes: 0

Views: 2197

Answers (2)

ryanmc
ryanmc

Reputation: 1881

I've yet to find a clean way to append data to sheets with xlsxwriter, so typically I create a temporary dataframe with all of the values, as well as current sheet if existing - then overwrite. I would definitely prefer if we could append to sheets as you attempted but it doesn't seem possible.

import pandas as pd
from pandas.io.data import DataReader
from datetime import datetime

symbols = ['GOOG','AAPL']

try:
    df = pd.read_excel('NewFile.xlsx')
except:
    df = pd.DataFrame()

for symbol in symbols:
    ClosingPrice = DataReader(symbol, 'yahoo', datetime(2015,1,1), datetime(2015,9,1))
    ClosingPrice = ClosingPrice.reset_index()
    ClosingPrice['Symbol'] = symbol
    df = df.append(ClosingPrice)

writer = pd.ExcelWriter('NewFile.xlsx', engine='xlsxwriter')
df.to_excel(writer,sheet_name='Sheet1',index=False)
writer.save()

If you were later appending to this same file, it would be ok:

df = pd.read_excel('NewFile.xlsx')
symbols = ['G']

for symbol in symbols:
    ClosingPrice = DataReader(symbol, 'yahoo', datetime(2015,1,1), datetime(2015,9,1))
    ClosingPrice = ClosingPrice.reset_index()
    ClosingPrice['Symbol'] = symbol
    df = df.append(ClosingPrice)

writer = pd.ExcelWriter('NewFile.xlsx', engine='xlsxwriter')
df.to_excel(writer,sheet_name='Sheet1',index=False)
writer.save()

Upvotes: 1

ryanmc
ryanmc

Reputation: 1881

What is the error you are getting? Have you tried Pandas dataframe.to_excel?

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.to_excel.html

Upvotes: 1

Related Questions