Pandas_datareader provides convenient way to extract data from various Internet sources. It directly load the data into Pandas. Just note, pandas uses memory for storage. Thus, if the result is large, we may seperate the result and store it into hard drive or process aggregation.
In [1]:
import pandas_datareader.data as web
import pandas as pd
import numpy as np
import datetime as dt

E:\Anaconda3\lib\site-packages\pandas_datareader\compat\__init__.py:7: FutureWarning: pandas.util.testing is deprecated. Use the functions in the public API at pandas.testing instead.
  from pandas.util.testing import assert_frame_equal
Currently, pandas_datareader has following data sources:
  1. Financial data: Tiingo,IEX,Alpha Vantage,Quandl,Fama/French, TSP Fund data,Nasdaq Trader Symbol Definitions,Stooq Index Data,MOEX Data
  2. Economic data: Econdbj, St.Louis FED (FRED),World Bank,OECD
  3. Small Business data: Enigma
  4. Rail Accidents data: Eurostat
In the operational loss calculation, it is important to use CPI (Consumer Price Index) adjusting historical losses. Below is an example showing how to download CPI data online directly from Federal Reserve Bank of St. Louis and then to calculate monthly and quarterly CPI adjustment factors with Python.
In [3]:
# SET START AND END DATES OF THE SERIES
sdt = dt.datetime(2019, 1, 1)
edt = dt.datetime(2021, 9, 1)
cpi = web.DataReader("CPIAUCNS", "fred", sdt, edt)
In [4]:
df1 = pd.DataFrame({'month': [dt.datetime.strftime(i, "%Y-%m") for i in cpi.index]})
df1['qtr'] = [str(x.year) + "-Q" + str(x.quarter) for x in cpi.index]
df1['m_cpi'] = cpi.values
df1.index = cpi.index
In [5]:
grp = df1.groupby('qtr', as_index = False)
df2 = grp['m_cpi'].agg({'q_cpi': np.mean})
In [6]:
df3 = pd.merge(df1, df2, how = 'inner', left_on = 'qtr', right_on = 'qtr')
maxm_cpi = np.array(df3.m_cpi)[-1]
maxq_cpi = np.array(df3.q_cpi)[-1]
df3['m_factor'] = maxm_cpi / df3.m_cpi
df3['q_factor'] = maxq_cpi / df3.q_cpi
df3.index = cpi.index
final = df3.sort_index(ascending = False)
final.head()
Out[6]:
month qtr m_cpi q_cpi m_factor q_factor
DATE
2020-05-01 2020-05 2020-Q2 256.394 256.391500 1.000000 1.000000
2020-04-01 2020-04 2020-Q2 256.389 256.391500 1.000020 1.000000
2020-03-01 2020-03 2020-Q1 258.115 258.254667 0.993332 0.992786
2020-02-01 2020-02 2020-Q1 258.678 258.254667 0.991170 0.992786
2020-01-01 2020-01 2020-Q1 257.971 258.254667 0.993887 0.992786