Filter on the closest available date to month-end in a Pandas dataframe
<p>Using python and pandas I am trying to download security price data from Yahoo Finance with the aim of ending up with the month-end adjusted price in a time series.</p>

<p>My code is shown below. I have used ix to filter the dataframe to produce a list of business month-end dates. This works for all but 2 dates in the time series, where 31 May 2010 and 29 March 2013 both appear as blanks, and I think is because these are federal holidays in the US.</p>

<p>Rather than going down the route of trying to create a calendar for trading days, is it possible to create a custom frequency or calendar that simply looks for the month-end date, and if it is not available, checks the previous dates until it finds a value? For example, 31 March 2013 has no data, so check for 30 March (no data), 29 March (no data), 28 March (data) -> display 28 March in series.</p>

<pre><code>import io
import requests
from datetime import datetime
import pandas

ticker = 'SPY'
start_date = '2009-12-31'
end_date = '2016-12-08'
s_dt = datetime.strptime(start_date, '%Y-%m-%d')
e_dt = datetime.strptime(end_date, '%Y-%m-%d')

url = 'http://chart.finance.yahoo.com/table.csv?s={0}&amp;a={1}&amp;b={2}&amp;c={3}&amp;d={4}&amp;e={5}&amp;f={6}&amp;g=d&amp;ignore=.csv'
url = url.format(ticker, s_dt.month-1, s_dt.day, s_dt.year, e_dt.month-1, e_dt.day, e_dt.year)
data = requests.get(url).content

df = pandas.read_csv(io.StringIO(data.decode('utf-8')))
df.drop('Open', 1, inplace=True)
df.drop('High', 1, inplace=True)
df.drop('Low', 1, inplace=True)
df.drop('Volume', 1, inplace=True)
df.drop('Close', 1, inplace=True)
df.columns = ['date', ticker]

df['date'] = pandas.to_datetime(df['date'], format='%Y-%m-%d')    
df = df.set_index('date')
df = df.ix[pandas.date_range(start=start_date, end=end_date, freq='BM')]
</code></pre>