Fetch Time Series from Quandl

Ulf Hamster 3 min.
python quandl time series financial data financial time series

Load Packages

import quandl
import pandas as pd
import numpy as np

Set API Key

Quandl requires an API key. You can store your API key in your $HOME folder in a flat file ~/.quandl_apikey and just call quandl.read_key() in all your scripts. See README.md and commit for Issue 116.

# Setup once - Store your Quandl API Key in your $HOME folder
# quandl.save_key("not telling you my API key")
# The API key is stored in a flat file
!ls -la ~/.quandl_apikey
# !cat ~/.quandl_apikey
# print(quandl.ApiConfig.api_key)  # still not telling you
-rw-r--r--  1 uh  staff  20 27 Oct 13:08 /Users/uh/.quandl_apikey
# Read the API key from the local flat file
quandl.read_key()
#print(quandl.ApiConfig.api_key)  # Uncomment to check you API key reading. Don't expose it!

Define Time Series to Download

# The Quandl tickers
ticker = [
    "EIA/PET_RWTC_D",  # Cushing, OK WTI Spot Price FOB
    "ML/EMCTRI",  # Emerging Markets Corp Bonds TR Index
    "ML/TRI",  # US Corporate Bonds Total Return Index
    "NASDAQOMX/NQEU.4"  # NASDAQ Europe Index (NQEU) with dividends
]
# The date range
start_dt = "2011-01-01";
end_dt = "2013-12-31";

Download Data

%%time
df = quandl.get(ticker, start_date=start_dt, end_date=end_dt)
CPU times: user 1.06 s, sys: 36.7 ms, total: 1.09 s
Wall time: 3.69 s
df.head()
.dataframe tbody tr th {
    vertical-align: top;
}

.dataframe thead th {
    text-align: right;
}

print(df.index.dtype, "\n")
print(df.dtypes)
datetime64[ns] 

EIA/PET_RWTC_D - Value                 float64
ML/EMCTRI - BAMLEMCBPITRIV             float64
ML/TRI - BAMLCC0A0CMTRIV               float64
NASDAQOMX/NQEU - Total Market Value    float64
dtype: object

Preprocess Data

names = list(df.columns)

print(names)
['EIA/PET_RWTC_D - Value', 'ML/EMCTRI - BAMLEMCBPITRIV', 'ML/TRI - BAMLCC0A0CMTRIV', 'NASDAQOMX/NQEU - Total Market Value']
# Weekday Grid
t = pd.date_range(start=start_dt, end=end_dt, freq='D')
t = t[t.weekday <= 4]

print(t)
DatetimeIndex(['2011-01-03', '2011-01-04', '2011-01-05', '2011-01-06',
               '2011-01-07', '2011-01-10', '2011-01-11', '2011-01-12',
               '2011-01-13', '2011-01-14',
               ...
               '2013-12-18', '2013-12-19', '2013-12-20', '2013-12-23',
               '2013-12-24', '2013-12-25', '2013-12-26', '2013-12-27',
               '2013-12-30', '2013-12-31'],
              dtype='datetime64[ns]', length=782, freq=None)
# Map downloaded data 'df' to weekday grid 't'
px = pd.DataFrame(index=t).join(df, how='left');
px = px.values

print(px)
[[9.15900000e+01 2.70390000e+02 2.06260000e+03 8.25824609e+12]
 [8.93900000e+01 2.70720000e+02 2.06270000e+03 8.29784815e+12]
 [9.03000000e+01 2.70710000e+02 2.04900000e+03 8.19570117e+12]
 ...
 [9.99400000e+01 3.21900000e+02 2.41045000e+03 1.04840293e+13]
 [9.89000000e+01 3.22240000e+02 2.41735000e+03 1.05022959e+13]
 [9.81700000e+01 3.22080000e+02 2.41390000e+03 1.05097582e+13]]
# Continuous Returns
ret = np.log(px[1:,:] / px[:-1,:])
# Previous Tick Interpolation
ret[pd.isnull(ret)] = 0.0

print(ret)
[[-2.43132763e-02  1.21971518e-03  4.84813226e-05  4.78399484e-03]
 [ 1.01286413e-02 -3.69392165e-05 -6.66393497e-03 -1.23864522e-02]
 [-2.16049149e-02  1.84529134e-03  3.42020592e-03 -4.38448949e-03]
 ...
 [ 7.63362486e-03 -4.34823127e-04 -4.02333540e-04  1.53459076e-02]
 [-1.04607673e-02  1.05567123e-03  2.85844679e-03  1.74080663e-03]
 [-7.40856892e-03 -4.96647639e-04 -1.42820205e-03  7.10286433e-04]]

Save to file

np.savez('data/demo3.npz', t=t, px=px, ret=ret)

Load from File

with np.load('data/demo3.npz') as data:
    t2 = data['t']
    px2 = data['px']
    ret2 = data['ret']
t2[:7]
array(['2011-01-03T00:00:00.000000000', '2011-01-04T00:00:00.000000000',
       '2011-01-05T00:00:00.000000000', '2011-01-06T00:00:00.000000000',
       '2011-01-07T00:00:00.000000000', '2011-01-10T00:00:00.000000000',
       '2011-01-11T00:00:00.000000000'], dtype='datetime64[ns]')
px2[:7]
array([[9.15900000e+01, 2.70390000e+02, 2.06260000e+03, 8.25824609e+12],
       [8.93900000e+01, 2.70720000e+02, 2.06270000e+03, 8.29784815e+12],
       [9.03000000e+01, 2.70710000e+02, 2.04900000e+03, 8.19570117e+12],
       [8.83700000e+01, 2.71210000e+02, 2.05602000e+03, 8.15984587e+12],
       [8.80700000e+01, 2.71500000e+02, 2.06458000e+03, 8.11498416e+12],
       [8.92400000e+01, 2.71410000e+02, 2.06666000e+03, 8.01856036e+12],
       [9.11100000e+01, 2.71390000e+02, 2.06533000e+03, 8.11940422e+12]])
ret2[:7].round(3)
array([[-0.024,  0.001,  0.   ,  0.005],
       [ 0.01 , -0.   , -0.007, -0.012],
       [-0.022,  0.002,  0.003, -0.004],
       [-0.003,  0.001,  0.004, -0.006],
       [ 0.013, -0.   ,  0.001, -0.012],
       [ 0.021, -0.   , -0.001,  0.012],
       [ 0.008,  0.001, -0.001,  0.023]])