CODEWITHSUNDEEP
pythonexcelpandasdataframe
Jkiefn1
Jkiefn1

Reputation: 153

Import data headers from excel doc, search web using pandas, then export to specific row/column in same excel doc

I am trying to figure out how to import data from a particular excel sheet, search yahoo finance for information according to that data, then print the data received via yahoo (via pandas web.datareader) to a specific row/column in that same excel file.

This is what I have so far, but it doesn't accomplish what I'm setting out to do. This searches for information based off of inputs within the code, not inputs from an excel sheet, and exports the concatenated dataframe to a newly created spreadsheet, not a pre-existing one's specific row and columns.

import datetime as dt
from datetime import datetime
import pandas as pd
import pandas_datareader.data as web

start = dt.datetime.strptime("8/11/2017", "%m/%d/%Y")
end = dt.datetime.today()
headerlist = ('stock1 Open', 'stock1 Close', 'stock2 Open', 'stock2 Close', 
'stock3 Open', 'stock3 Close', 'stock4 Open', 'stock4 Close')

df1 = web.DataReader('stock1', 'yahoo', start, end)[['Open','Close']]
df2 = web.DataReader('stock2', 'yahoo', start, end)[['Open','Close']]
df3 = web.DataReader('stock3', 'yahoo', start, end)[['Open','Close']]
df4 = web.DataReader('stock4', 'yahoo', start, end)[['Open','Close']]

resultingdf = pd.concat([df1, df2, df3, df4], axis=1)
resultingdf.to_csv('Portfolio.csv', header = headerlist)

Any help or direction would be greatly greatly appreciated.

Edit:

The code provided above runs fine, it just isn't achieving the goal that I am setting out for as it is not automated in the least. It requires a ton of inputs in the code itself. Here is a general breakdown of what I'm trying to accomplish:

import datetime as dt
from datetime import datetime
import pandas as pd
import pandas_datareader.data as web
#import any other modules I may need

#establish timeframe
start = dt.datetime.strptime("8/11/2017", "%m/%d/%Y")
end = dt.datetime.today()

#search a pre-existing excel sheet's columns for stock tickers similar to: 
Name
AAA
BBB
CCC
DDD

#use panda's datareader to find the information for those tickers from yahoo, google, etc.

#concat. the dataframe

#export the dataframe to a specific row and column in the same excel sheet similar to (with identifying header):
Open   High   Low   Close,   Open   High   Low   Close

Hope that explains it a little bit better.

Upvotes: 1

Views: 437

Answers (1)

user8834780
user8834780

Reputation: 1670

This should do it:

import datetime as dt
from datetime import datetime
import pandas as pd
import pandas_datareader.data as web
from openpyxl import load_workbook

start = dt.datetime.strptime("8/11/2017", "%m/%d/%Y")
end = dt.datetime.today()

data_file = pd.ExcelFile('Stocks.xlsx',header=0).parse('Sheet1') #1st sheet
#print(data_file)

#datafile.columns is the excel's header row with stock 1, stock 2, etc. For example: FB, AAPL, etc.
stocks = web.DataReader(data_file.columns, 'yahoo', start, end)[['Open','Close']] #This creates a panel
#print(stocks)

#Writes to same workbookbook with data, but different tabs, where each column (Open, Close) is in a differen tab
#You can't "append" data to an existing sheet, only overwrite it in full including headers- so can't reuse next day
book = load_workbook('Stocks.xlsx')
writer= pd.ExcelWriter('Stocks.xlsx', engine='openpyxl')
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
stocks.to_excel(writer)
writer.save()

Alternatively, you can create new excel with results, where each column (Open, Close) is in a different tab:

writer= pd.ExcelWriter('Stock Data.xlsx', engine="xlsxwriter")
stocks.to_excel(writer) #,index=False)
writer.save()

You can even create a new file with new data every day you run it, by specifying name +'xslx' in pd.ExcelWriter where

now = datetime.datetime.now()
name = 'Opportunistic_leads_' + str(now)[:10]

Take a look here: iteratively calling pandas datareader at some ways to transform output of stocks. This will apply when you export the panel to excel.

Note that I wasn't able to find a way to append data to an existing excel sheet without overwriting it, but this is as close to what you want as I could do it.

FYI, when you export a Panel (which is resulted from DataReader) to excel, it creates each "column" in a new tab as per https://pandas.pydata.org/pandas-docs/stable/generated/pandas.Panel.to_excel.html:

Upvotes: 2

Related Questions