calculate YTD return / find first available datapoint of a year in python

Question

I need to calculate the year-to-date relative return of a given dataset. I usually caculate the cumulative relative return with this simple function:

def RelPerf(price):
    RelPerf = (price/price[0])
    return RelPerf  

The problem ist that I need to set instead of "price[0]" the price by the start of each year (first available datapoint of the year). Since the dataset does not contain data for each day of the year I can't simply use sth like +365. So the question is how do I get dynamically the location of the first available datapoint into the formula?

This is a short example of the dataframe used:

              CLOSE_SPX    Close_iBoxx  A_Returns  B_Returns  A_Vola    B_Vola
2014-05-15    1870.85      234.3017    -0.009362   0.003412   0.170535  0.075468   
2014-05-16    1877.86      234.0216     0.003747  -0.001195   0.170153  0.075378
2014-05-19    1885.08      233.7717     0.003845  -0.001068   0.170059  0.075384   
2014-05-20    1872.83      234.2596    -0.006498   0.002087   0.170135  0.075410   
2014-05-21    1888.03      233.9101     0.008116  -0.001492   0.169560  0.075326   
2014-05-22    1892.49      233.5429     0.002362  -0.001570   0.169370  0.075341   
2014-05-23    1900.53      233.8605     0.004248   0.001360   0.168716  0.075333   
2014-05-27    1911.91      234.0368     0.005988   0.000754   0.168797  0.075294   
2014-05-28    1909.78      235.4454    -0.001114   0.006019   0.168805  0.075474   
2014-05-29    1920.03      235.1813     0.005367  -0.001122   0.168866  0.075451   
2014-05-30    1923.57      235.2161     0.001844   0.000148   0.168844  0.075430   
2014-06-02    1924.97      233.8868     0.000728  -0.005651   0.168528  0.075641   
2014-06-03    1924.24      232.9049    -0.000379  -0.004198   0.167852  0.075267

Answer 1

You need to use a groupby, timegrouper, and its annual option, and take the first value in each group.

For example, if I grab yahoo data:

import pandas as pd
import pandas.io.data as web

# Grab S&P 500 data going back to beginning of 2011
SPY_Dat = web.DataReader('SPY', 'yahoo', datetime.date(2011,1,1), end)

# Convert to annual data:
SPY_Ann_Dat = SPY_Dat.groupby(pd.TimeGrouper('A')).nth(0)

In this case we are using the Annual time grouper ('A'). We could also use monthly (M), quarterly (Q), or weekly (W).

Interestingly, nth can also use negative indexing, so if we wanted to get the last day of each year, we could change the last line to:

SPY_Last_Ann_Dat = SPY_Dat.groupby(pd.TimeGrouper('A')).nth(-1)

So, now if we wanted to, as in your example, calculate the YTD return, we can tweak things a bit, to not just grab the first date, but apply a transformation using the data from the first date in each group:

import pandas as pd
import pandas.io.data as web

# Grab S&P 500 data going back to beginning of 2011
SPY_Dat = web.DataReader('SPY', 'yahoo', datetime.date(2011,1,1))

# Group the data with the same TimeGrouper to get things grouped by year
SPY_GroupedDat = SPY_Dat.groupby(pd.TimeGrouper('A'))

# Create a new column with YTD data of adjusted close, using a transformation lambda function applied to our group data.
SPY_Dat["YTD"] = SPY_GroupedDat['Adj Close'].transform(lambda x: x/x.iloc[0]-1.0)

This will yield a column in SPY_Dat which is a running YTD return, reset on the first trading date of each year.

Answer 2

The code looks a bit dated, as grouper now requires level and frequency.

Then, pandas-datareader gets choked on Yahoo and Google.

The script below works, using quandl as source.

import pandas as pd
pd.core.common.is_list_like = pd.api.types.is_list_like # Solving a pandas-datareader v0.6.0 problem
import pandas_datareader.data as web
from datetime import datetime

start = datetime(2011, 1, 1)
end = datetime(2018, 1, 1)

Grab S&P 500 data going back to beginning of 2011 SPY_Dat = web.DataReader('AAPL.US', 'quandl', start, end)

Convert to annual data:

SPY_Ann_Dat      = SPY_Dat.groupby(pd.Grouper(level='Date', freq='A')).nth(0)

Alternatively:

SPY_Last_Ann_Dat = SPY_Dat.groupby(pd.Grouper(level='Date', freq='A')).nth(-1)

Group the data with the same TimeGrouper to get things grouped by year

SPY_GroupedDat = SPY_Dat.groupby(pd.Grouper(level='Date', freq='A'))

Create a new column with YTD data of adjusted close, using a transformation lambda function applied to our group data.

SPY_Dat["YTD"] = SPY_GroupedDat['AdjClose'].transform(lambda x: x/x.iloc[0]-1.0)

Answer 3

In case any years data is missing, it gives error.

It can be corrected as below :

df["YTD"] = df1_GroupedDat['Adj Close'].transform(lambda x: 0 if x.empty else x/x.iloc[0]-1.0)