Retrieving data from Quandl with Python

Question

How can I get the latest prices from a Quandl dataset with the Python API (https://www.quandl.com/help/python)? On https://www.quandl.com/help/api, it says "You can use rows=n to get only the first n rows of your dataset. Use rows=1 to get the latest observation for any dataset." but if i use rows=1 I will get the first observation instead of the latest.

Besides, I need to get exchange rates for USD but from https://www.quandl.com/resources/api-for-currency-data it seems that I need to retrieve the exchange rates for each currency instead of just having a dataset with all the most recent exchange rates for each currency versus USD. Isn't this possible?

Answer 1

import Quandl

You first need to sort the dataset in descending order to get the most recent value:

Quandl.get("FRED/DEXUSEU", rows=1, sort_order='desc')
             Value
Date              
2015-05-15  1.1428

You also need to request the exchange rate separately for each currency:

fred_rates = pd.DataFrame({'Currency': {'DEXBZUS': 'Brazilian Real (BRL)',
                                        'DEXCAUS': 'Canadaian Dollar (CAD)',
                                        'DEXCHUS': 'Chinese Yuan (CNY))',
                                        'DEXDNUS': 'Denish Krone (DKK)',
                                        'DEXHKUS': 'Hong Kong Dollar (HKD)',
                                        'DEXINUS': 'Indian Rupee (INR)',
                                        'DEXJPUS': 'Japanese Yen (JPY)',
                                        'DEXKOUS': 'South Korean Won (KRW)',
                                        'DEXMAUS': 'Malaysian Ringgit (MYR)',
                                        'DEXMXUS': 'Mexican Peso (MXN)',
                                        'DEXNOUS': 'Norwegian Krone(NOK)',
                                        'DEXSDUS': 'Swedish Krona (SEK)',
                                        'DEXSFUS': 'South African Rand(ZAR)',
                                        'DEXSIUS': 'Singapore Dollar (SGD)',
                                        'DEXSLUS': 'Sri Lankan Rupee(LKR)',
                                        'DEXSZUS': 'Swiss Franc (CHF)',
                                        'DEXTAUS': 'New Taiwan Dollar (TWD)',
                                        'DEXTHUS': 'Thai Baht (THB)',
                                        'DEXUSAL': 'Australian Dollar (AUD)',
                                        'DEXUSEU': 'Euro (EUR)',
                                        'DEXUSNZ': 'New Zealand Dollar (NZD)',
                                        'DEXUSUK': 'British Pound (GBP)',
                                        'DEXVZUS': 'Venezuelan Bolivar (VEF)'}})
fred_rates['symbol'] = frates.Currency.map(lambda x: x[-4:-1])

rates = [Quandl.get("FRED/{0}".format(fx)) for fx in fred_rates.index]
fx_rates = pd.concat(rates, axis=1)
fx_rates.columns = [fx for fx in fred_rates.symbol]

>>> fx_rates.info()
<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: 11168 entries, 1971-01-04 to 2015-05-15
Data columns (total 23 columns):
AUD    11130 non-null float64
BRL    5120 non-null float64
GBP    11137 non-null float64
CAD    11143 non-null float64
NY)    8577 non-null float64
DKK    11145 non-null float64
EUR    4116 non-null float64
HKD    8637 non-null float64
INR    10629 non-null float64
JPY    11131 non-null float64
MYR    11115 non-null float64
MXN    5405 non-null float64
TWD    7650 non-null float64
NZD    11121 non-null float64
NOK    11136 non-null float64
SGD    8636 non-null float64
ZAR    11110 non-null float64
KRW    8523 non-null float64
LKR    10277 non-null float64
SEK    11136 non-null float64
CHF    11137 non-null float64
THB    8556 non-null float64
VEF    5114 non-null float64
dtypes: float64(23)
memory usage: 2.0 MB

>>> fx_rates.tail()
               AUD     BRL     GBP     CAD     NY)  DKK     EUR     HKD  Date                                                                      
2015-05-11  0.7899  3.0385  1.5593  1.2107  6.2086  NaN  1.1142  7.7535   
2015-05-12  0.7989  3.0223  1.5685  1.1987  6.2086  NaN  1.1240  7.7528   
2015-05-13  0.8118  3.0265  1.5748  1.1950  6.2043  NaN  1.1372  7.7517   
2015-05-14  0.8082  2.9910  1.5766  1.1991  6.2013  NaN  1.1368  7.7505   
2015-05-15  0.8053  2.9779  1.5772  1.2009  6.2051  NaN  1.1428  7.7505   

              INR     JPY   ...       NZD     NOK     SGD      ZAR      KRW  Date                        ...                                               
2015-05-11  63.96  120.05   ...    0.7350  7.5605  1.3361  12.0820  1095.39   
2015-05-12  64.19  119.80   ...    0.7377  7.4720  1.3336  12.0430  1093.81   
2015-05-13  63.88  119.09   ...    0.7488  7.3597  1.3239  11.8760  1089.72   
2015-05-14  63.47  119.20   ...    0.7500  7.3829  1.3199  11.8220  1089.46   
2015-05-15  63.36  119.36   ...    0.7489  7.3113  1.3195  11.7645  1083.05   

              LKR     SEK     CHF    THB     VEF  
Date                                              
2015-05-11  133.3  8.2950  0.9344  33.71  6.2842  
2015-05-12  133.5  8.3022  0.9266  33.70  6.2842  
2015-05-13  133.5  8.2085  0.9162  33.51  6.2842  
2015-05-14  133.4  8.2531  0.9146  33.50  6.2842  
2015-05-15  133.4  8.2174  0.9174  33.48  6.2842  

[5 rows x 23 columns]