Applying a Month End Trading Calendar to Yahoo API data

Question

This is my first post, and I am new to Python and Pandas. I have been working on piecing together the code below based on many questions and answers I have viewed on this website. My next challenge is how to apply a month end trading calendar to the code below so that the output consists of month end "Adj Close" values for the two ETFs listed "VTI and BND". The "100ma" 100 day moving average must still be calculated based on the previous 100 trading days.

@ryan sheftel appears to have something on this site that would work, but I can't seem to implement it with my code to give me what I want.

Create trading holiday calendar with Pandas

Code I have put together so far:

import datetime as dt  #set start and end dates for data we are using
import pandas as pd
import numpy as np
import pandas_datareader.data as web # how I grab data from Yahoo Finance API. Pandas is popular data analysis library.

start = dt.datetime(2007,1,1)
end = dt.datetime(2017,2,18)

vti = web.DataReader('vti', 'yahoo',start, end)# data frame, stock ticker symbol, where getting from, start time, end time
bnd = web.DataReader('bnd', 'yahoo', start, end)

vti["100ma"] = vti["Adj Close"].rolling(window=100).mean()
bnd["100ma"] = bnd["Adj Close"].rolling(window=100).mean()

# Below I create a DataFrame consisting of the adjusted closing price of these stocks, first by making a list of these objects and using the join method
stocks = pd.DataFrame({'VTI': vti["Adj Close"],  
                       'VTI 100ma': vti["100ma"],
                       'BND': bnd["Adj Close"],
                        'BND 100ma': bnd["100ma"],
                       })

print (stocks.head())

stocks.to_csv('Stock ETFs.csv')

Answer 1

I'd use asfreq to sample down to business month

import datetime as dt  #set start and end dates for data we are using
import pandas as pd
import numpy as np
import pandas_datareader.data as web # how I grab data from Yahoo Finance API. Pandas is popular data analysis library.

start = dt.datetime(2007,1,1)
end = dt.datetime(2017,2,18)

ids = ['vti', 'bnd']
data = web.DataReader(ids, 'yahoo', start, end)

ac = data['Adj Close']
ac.join(ac.rolling(100).mean(), rsuffix=' 100ma').asfreq('BM')

                  bnd        vti  bnd 100ma  vti 100ma
Date                                                  
2007-01-31        NaN  58.453726        NaN        NaN
2007-02-28        NaN  57.504188        NaN        NaN
2007-03-30        NaN  58.148760        NaN        NaN
2007-04-30  54.632232  60.487535        NaN        NaN
2007-05-31  54.202353  62.739991        NaN  59.207899
2007-06-29  54.033591  61.634027        NaN  60.057136
2007-07-31  54.531996  59.455505        NaN  60.902113
2007-08-31  55.340892  60.330213  54.335640  61.227386
2007-09-28  55.674840  62.650936  54.542452  61.363872
2007-10-31  56.186500  63.773849  54.942038  61.675567