vsoler vsoler - 1 year ago 44
Python Question

Pandas: Charting 3 months of quotes with a benchmark

I have a df with the daily "Adj Close" (from Yahoo) for many stocks that I want to chart compared to the SPY.

2016-03-10 100.56 85.79 198.52
2016-03-11 101.64 89.14 201.72
2016-06-09 99.65 76.17 212.08
2016-06-10 98.83 76.46 210.07

I want to chart the last 3 months for each stock except SPY (the benchmark).
Each chart should compare the prices of the stock to the prices of the SPY.

This means that, for example, for charting AAPL, the benchmark SPY_AAPL should be calculated as:

SPY_AAPL = df['SPY'] / 198.52 * 100.56

...so that the first point of AAPL hovers over that of "SPY_AAPL".
Charting the other stocks requires similar calculations...

SPY_ABC = df['SPY'] / 198.52 * 85.79

I am not sure the auxiliary columns (SPY_AAPL, SPT_ABC...) must be added to the df. Perpaps they can be calculated "on the fly".

The "family of charts" could perhaps be in a "for loop"

for x in df.columns: # how to remove the SPY here?
df[x].tail(66).plot() # 66 is approx 3 months
# SPY_XXX.tail(66).plot() # here the benchmark

Any help is highly appreciated.


Are you looking for something along these lines - (divides all prices by the first row):

stockData = DataReader(['AAPL', 'AMZN', 'FB', 'SPY'], 'yahoo', datetime(2015, 1, 1), datetime.today()).loc['Adj Close']
stockData = stockData.div(stockData.iloc[0], axis=1)
for stock in stockData.columns[:-1]:
    stockData.loc[:, [stock, 'SPY']].plot()

enter image description here