I am working on some portfolio analysis and am trying to get a working function for pulling data for stocks, using a list of Ticker Symbols. Here is my list:
Ticker_List={'Tickers':['SPY', 'AAPL', 'TSLA', 'AMZN', 'BRK.B', 'DAL', 'EURN', 'AMD',
'NVDA', 'SPG', 'DIS', 'SBUX', 'MMP', 'USFD', 'CHEF', 'SYY',
'GOOGL', 'MSFT']}
I'm passing the list through this function like so:
Port=kit.d(Ticker_List)
def d(Ticker_List):
x=[]
for i in Ticker_List['Tickers']:
x.append(Closing_price_alltime(i))
return x
def Closing_price_alltime(Ticker):
Closedf=td_client.get_price_history(Ticker, period_type='year', period=20, frequency_type='daily', frequency=1)
return Closedf
Which pulls data from TDAmeritrade and gives me back:
[{'candles': [{'open': 147.46875,'high': 148.21875,
'low': 146.875,'close': 147.125,
'volume': 6998100,'datetime': 960181200000},
{'open': 146.625,'high': 147.78125,
'low': 145.90625,'close': 146.46875,
'volume': 4858900,'datetime': 960267600000},
...],
'symbol': 'MSFT',
'empty': False}]`
(This is just a sample of course)
Finally, I'm cleaning up with:
Port=pd.DataFrame(Port)
Port=pd.DataFrame.drop(Port, columns='empty')`
Which gives the DataFrame:
candles symbol
0 [{'open': 147.46875, 'high': 148.21875, 'low': 146.875, 'close': 147.125, 'volume': 6998100, 'datetime': 960181200000}, {'open': 146.625, 'high': ...} SPY
1 [{'open': 3.33259, 'high': 3.401786, 'low': 3.203126, 'close': 3.261161, 'volume': 80917200, 'datetime': 960181200000}, {'open': 3.284599, 'high':...} AAPL
How can I get the close price out of the nested dictionary in each row and set that as a the columns, with the ticker symbols (currently in their own column) as the headers for the closing price columns. Also how to extract the datetime from each nested dictionary and set it as the index.
EDIT: More info
My original method of building this DataFrame was:
SPY_close=kit.Closing_price_alltime('SPY')
AAPL_close=kit.Closing_price_alltime('AAPL')
TSLA_close=kit.Closing_price_alltime('TSLA')
AMZN_close=kit.Closing_price_alltime('AMZN')
BRKB_close=kit.Closing_price_alltime('BRK.B')
DAL_close=kit.Closing_price_alltime('DAL')
EURN_close=kit.Closing_price_alltime('EURN')
AMD_close=kit.Closing_price_alltime('AMD')
NVDA_close=kit.Closing_price_alltime('NVDA')
SPG_close=kit.Closing_price_alltime('SPG')
DIS_close=kit.Closing_price_alltime('DIS')
SBUX_close=kit.Closing_price_alltime('SBUX')
MMP_close=kit.Closing_price_alltime('MMP')
USFD_close=kit.Closing_price_alltime('USFD')
CHEF_close=kit.Closing_price_alltime('CHEF')
SYY_close=kit.Closing_price_alltime('SYY')
GOOGL_close=kit.Closing_price_alltime('GOOGL')
MSFT_close=kit.Closing_price_alltime('MSFT')
def Closing_price_alltime(Ticker):
"""
Gets Closing Price for Past 20 Years w/ Daily Intervals
and Formats it to correct Date and single 'Closing Price'
column.
"""
Raw_close=td_client.get_price_history(Ticker,
period_type='year', period=20, frequency_type='daily', frequency=1)
#Closedf = pd.DataFrame(Raw_close['candles']).set_index('datetime')
#Closedf=pd.DataFrame.drop(Closedf, columns=['open', 'high',
'low', 'volume'])
#Closedf.index = pd.to_datetime(Closedf.index, unit='ms')
#Closedf.index.names=['Date']
#Closedf.columns=[f'{Ticker} Close']
#Closedf=Closedf.dropna()
return Closedf
SPY_pct=kit.pct_change(SPY_close)
AAPL_pct=kit.pct_change(AAPL_close)
TSLA_pct=kit.pct_change(TSLA_close)
AMZN_pct=kit.pct_change(AMZN_close)
BRKB_pct=kit.pct_change(BRKB_close)
DAL_pct=kit.pct_change(DAL_close)
EURN_pct=kit.pct_change(EURN_close)
AMD_pct=kit.pct_change(AMD_close)
NVDA_pct=kit.pct_change(NVDA_close)
SPG_pct=kit.pct_change(SPG_close)
DIS_pct=kit.pct_change(DIS_close)
SBUX_pct=kit.pct_change(SBUX_close)
MMP_pct=kit.pct_change(MMP_close)
USFD_pct=kit.pct_change(USFD_close)
CHEF_pct=kit.pct_change(CHEF_close)
SYY_pct=kit.pct_change(SYY_close)
GOOGL_pct=kit.pct_change(GOOGL_close)
MSFT_pct=kit.pct_change(MSFT_close)
def pct_change(Ticker_ClosingValues):
"""
Takes Closing Values and Finds Percent Change.
Closing Value Column must be named 'Closing Price'.
"""
return_pct=Ticker_ClosingValues.pct_change()
return_pct=return_pct.dropna()
return return_pct
Portfolio_hist_rets=[SPY_pct, AAPL_pct, TSLA_pct, AMZN_pct,
BRKB_pct, DAL_pct, EURN_pct, AMD_pct,
NVDA_pct, SPG_pct, DIS_pct, SBUX_pct,
MMP_pct, USFD_pct, CHEF_pct, SYY_pct,
GOOGL_pct, MSFT_pct]
Which returned exactly what I wanted:
SPY Close AAPL Close TSLA Close AMZN Close BRK.B Close
Date
2000-06-06 05:00:00 -0.004460 0.017111 NaN -0.072248 -0.002060
2000-06-07 05:00:00 0.006934 0.039704 NaN 0.024722 0.013416
2000-06-08 05:00:00 -0.003920 -0.018123 NaN 0.001206 -0.004073
This method is obviously much less efficient than just using a for loop to create a DataFrame from a list of tickers.
In short, I'm asking what changes can be made to my new code (above my edit) to achieve the same end result as my old code (below my edit) (a well formatted and labeled DataFrame).
This is the final function I wrote which accomplishes my goal:
def Port_consol(Ticker_List):
"""
Consolidates Ticker Symbol Returns and Returns
a Single Portolio
"""
Port=[]
Port_=[]
for i in Ticker_List['Tickers']:
Port.append(Closing_price_alltime(i))
j=list(range(0, (n_assets)))
for i in j:
data = operator.itemgetter('datetime','close')
symbol = Port[i]['symbol']
candles = Port[i]['candles']
dt, closing = zip(*map(data, candles))
s = pd.Series(data=closing,index=dt,name=symbol)
s=pd.DataFrame(s)
s.index = pd.to_datetime(s.index, unit='ms')
Port_.append(s)
Portfolio=pd.concat(Port_, axis=1, sort=False)
return Portfolio
I can now pass though a list of tickers to this function, the data will be pulled from TDAmeritrade's API (using python package td-ameritrade-python-api), and a DataFrame is formed with historical closing prices for the Stocks whose tickers I pass through.