Analysis of last year monthly performance

Back to Community

edited

Hi,

At the beginning of each new year, I would like the algo to tell me what were the best X months of last year.

It is easy with python in Jupyter notebook as I can use monthly frequency when I download Yahoo quote but I am struggling when I try to implement the same thing in an algo.
Any idea ?

Below is an example of what I would like to do in my algo:
(This will print the best five month of year 2013)

import pandas as pd  
import numpy as np

df = pd.read_csv('SPY_2013_monthly.csv')  
df['Return'] = (df['Close'] / df['Open']) - 1  
df['month'] = df['Date'].map(lambda x: str(x)[5:-3])

col_useful = ['month', 'Return']  
df = df[col_useful]

df = df.groupby('month').sum()

df = df.sort_values(by='Return', ascending=0)

df[0:5]

output:

month Return
10 --> 0.139218
01 --> 0.075043
02 --> 0.070630
11 --> 0.056078
03 --> 0.055084

Thank you in advance.
Chris

2 responses

Cory Magnuson

The below will group returns by month, prints to the log.
It calculates the last 252 trading days worth of returns.
Hope it helps.

import pandas as pd  
import numpy as np  
def initialize(context):  
    # Rebalance every day, 1 hour after market open.  
    schedule_function(get_data, date_rules.every_day(), time_rules.market_open(hours=1))  
    context.security = symbols('SPY')  
def get_data(context, data):  
    #Pulls daily history of prices for the last 252 trading days  
    price_detail = data.history(context.security,"close", 252, "1d")  
    #Calculates the daily log return - log returns can be added without adjustment which makes it easier to get the total by month  
    return_detail = np.log(price_detail / price_detail.shift(1))

    #Gets rid of the first NaN row  
    return_detail = return_detail[1:]  
    #Groups log return by month and adds them  - Multiplied by 100 to make it more readable, but you can take that out if you want.  
    mthly = return_detail.groupby(pd.TimeGrouper(freq='M')).sum() * 100  
    #Prints the monthly returns in the log  
    print(mthly)

Log output looks like this:
2015-10-31 00:00:00+00:00 3.062946
2015-11-30 00:00:00+00:00 0.388472
2015-12-31 00:00:00+00:00 -1.704345
2016-01-31 00:00:00+00:00 -5.126184
2016-02-29 00:00:00+00:00 -0.129024
2016-03-31 00:00:00+00:00 6.565185
2016-04-30 00:00:00+00:00 0.379192
2016-05-31 00:00:00+00:00 1.710215
2016-06-30 00:00:00+00:00 0.328199
2016-07-31 00:00:00+00:00 3.577413
2016-08-31 00:00:00+00:00 0.114728
2016-09-30 00:00:00+00:00 0.033746
2016-10-31 00:00:00+00:00 0.078542

D. Chris

Awesome.
Thank you very much Cory.

You've successfully submitted a support ticket.

Our support team will be in touch soon.