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?
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]
