How to get currency exchange rates in R
You can use quantmod to get yahoo quotes. (I'm not sure how delayed yahoo FX quotes are, or how often they're updated.)
library(quantmod)
from <- c("CAD", "JPY", "USD")
to <- c("USD", "USD", "EUR")
getQuote(paste0(from, to, "=X"))
# Trade Time Last Change % Change Open High Low Volume
#CADUSD=X 2014-11-01 08:23:00 0.8875 N/A N/A N/A N/A N/A N/A
#JPYUSD=X 2014-11-01 08:23:00 0.0089 N/A N/A N/A N/A N/A N/A
#USDEUR=X 2014-11-01 08:23:00 0.7985 N/A N/A N/A N/A N/A N/A
Or TFX for real-time, millisecond timestamped quotes if you sign up for a free account. (note you have to use market convention; i.e. USD/JPY instead of JPY/USD)
library(TFX)
pairs <- paste(to, from, sep="/")
QueryTrueFX(ConnectTrueFX(pairs, "validUser", "anytext"))
# Symbol Bid.Price Ask.Price High Low TimeStamp
#1 USD/CAD 1.12651 1.12665 1.12665 1.12651 2014-10-31 20:45:00.559
#2 USD/JPY 112.34600 112.35900 112.35900 112.34600 2014-10-31 20:45:00.134
#3 EUR/USD 1.25234 1.25253 1.25253 1.25234 2014-10-31 20:45:00.598
Or if you have an Interactive Brokers account, you can use the IBrokers package, or my twsInstrument package (which is basically just wrappers for IBrokers functions)
library(twsInstrument)
getQuote(paste0(to, from), src="IB") # only works when market is open.
Function to convert from one currency to another currency in R
Here is a function, it does suffer from slight rounding error but just needs numbers with greater resolution to reduce that error - my values come from google searches of 10000 USD in each currency. You could also look at packages that scrape values from the web (rvest?) if you want to keep values updated automatically.
currencyCon <- function(x, from = "USD", to = "EUR"){
# assign values: 1 usd in each currency
values <- c(1.000000, 0.927985, 0.810100, 107.624500)
# names attribute
names(values) <- c("USD", "EUR", "GBP", "YEN")
# calculate (convert from into USD, and divide to)
values[to] / (values[from] / x)
}
# Testing
currencyCon(1, "USD", "EUR")
currencyCon(1, "EUR", "EUR")
currencyCon(1, "GBP", "YEN")
This returns
> currencyCon(1, "USD", "EUR")
EUR
0.927985
> currencyCon(1, "EUR", "EUR")
EUR
1
> currencyCon(1, "GBP", "YEN")
YEN
132.8534
Exchange Rate for specific date and currency on a data frame
Solution:
#Data frame
df <- data.frame(
currency = c("USD", "DKK", "USD", NA),
price = c(10, 11, 12, 13),
date = as.Date(c("2020-01-01", "2020-02-01", "2020-03-01", "2020-04-01"))
)
head(df)
#Function to pull conversions
avg_ex <- function(x, y){
if (is.na(x)) 0
else historical_exchange_rates(x, to = "EUR", start_date = y, end_date = y) %>%
`colnames<-`(c('date','conv')) %>% summarise(conv) %>% as.numeric
}
#Apply across all needed
df$conv = mapply(avg_ex, df$currency, df$date) %>% data.frame() %>% rownames_to_column() %>%
`colnames<-`(c('currency','conv'))
df
How can I get foreign exchange rates thru an API?
Try by using CurrencyFreaks API. Its key features are:
Reliable data as they source their data from various trusted Forex exchanges and
Banks.You can get updates every 60 sec.
You can change the ‘Base’ currency.
It provides currency exchange rates for 179 currencies worldwide including currencies, metals, and cryptocurrencies.
Supported codes for the endpoints are Shell, Node.js, Java, Python,
PHP, Ruby, JS, C#, Go, C, Swift.
Here is the latest exchange rates endpoint:
$ curl 'https://api.currencyfreaks.com/latest?apikey=YOUR_APIKEY'
The JSON response will be as follows:
{
"date": "2020-07-02 10:39:00+00",
"base": "USD",
"rates": {
"FJD": 2.1692,
"MXN": 22.602,
"STD": 22000.6197,
"SCR": 17.6,
"LVL": 0.6563,
"CDF": 1907.87,
"BBD": 2.0,
"GTQ": 7.7,
"CLP": 808.6,
"UGX": 3721.0,
"HNL": 24.7553,
"ZAR": 16.9326,
"TND": 2.8445,
"CUC": 1.0,
"SLL": 9778.35,
"BSD": 1.0,
[...]
} }
You can also get live currency exchange rates from here: https://currencyfreaks.com/exchange-rates
Hope it will help you.
Convert into EURO from multiple currencies in R
If I understand your question, you simply want to add a column to the existing dataset that contains the price in euro. I think you have the right idea with joining datasets, but because of the way that function you provided formats the answer there's just a few ugly things that need tending to. I also imagine you'd like a way of generalizing this to larger similarly structured datasets and so funtion-izing it is preferable.
I'm sure there's a more efficient way, but for a tidyphile like me this works.
library(priceR);library(tidyverse)
#Data frame
df <- data.frame(
currency = c("NZD", "NZD", "NZD", "NZD", "NZD", "EUR", "SEK", "EUR"),
price = c(580.9, 539.75, 567.8, 802, 486, 365, 5088, 111)
)
#Function to pull conversions
avg_ex <- function(x){
historical_exchange_rates(x, to = "EUR",start_date = "2019-01-01", end_date = "2019-12-31") %>%
`colnames<-`(c('date','conv')) %>% summarise(mean(conv)) %>% as.numeric
}
#Apply across all needed
conversions = sapply(unique(df$currency),avg_ex) %>% data.frame() %>% rownames_to_column() %>%
`colnames<-`(c('currency','conv'))
#Join and convert
df %>% left_join(conversions,by='currency') %>%
mutate(price_euro = price*conv)
Which generates the following output
currency price conv price_euro
1 NZD 580.90 0.58865122 341.9475
2 NZD 539.75 0.58865122 317.7245
3 NZD 567.80 0.58865122 334.2362
4 NZD 802.00 0.58865122 472.0983
5 NZD 486.00 0.58865122 286.0845
6 EUR 365.00 1.00000000 365.0000
7 SEK 5088.00 0.09452367 480.9364
8 EUR 111.00 1.00000000 111.0000
Currency exchange for multiple currencies on different dates
With functions ?merge,?lapply and following the steps mentioned below:
Steps:
1) create currency pairs EUR/GBP,USD/GBP
2) get FX data
3) reshape/modify FX data
4) merge with original data and calculate fx translated amount
library("quantmod")
library("reshape2") #for melt function
#replaced CAN by CAD for Canadian Dollar
currDF <- data.frame(
date = seq.Date(from = as.Date('2017-01-01'), to = as.Date('2017-01-10'), length = 10),
currency = c('EUR', 'GBP', 'USD', 'HKD', 'GBP', 'EUR', 'CAD', 'AUD', 'EUR', 'GBP'),
amount = 1:10,stringsAsFactors=FALSE)
currDF
FX Data:
#Set start and end dates
startDt = as.Date("2017-01-01")
endDt = as.Date("2017-01-10")
#create currency pair combinations i.e. EUR/GBP, USDGBP
currCombinations = paste(setdiff(unique(currDF$currency),"GBP"),"GBP",sep="/")
currCombinations
#[1] "EUR/GBP" "USD/GBP" "HKD/GBP" "CAD/GBP" "AUD/GBP"
#get FX data for each currency pair and merge them into a single xts dataset
#see ?lapply, ?do.call and ?merge.xts
#note auto.assign needs to be FALSE
fxData = do.call(merge.xts,lapply(currCombinations,function(x)
getFX(x,from=startDt,to=endDt,auto.assign=FALSE)))
fxData
# EUR.GBP USD.GBP HKD.GBP CAD.GBP AUD.GBP
#2017-01-01 0.852550 0.810242 0.104474 0.602773 0.583503
#2017-01-02 0.852550 0.810242 0.104474 0.602773 0.583503
#2017-01-03 0.850220 0.814326 0.104987 0.606361 0.587630
#2017-01-04 0.850310 0.815139 0.105102 0.609074 0.591082
#2017-01-05 0.853695 0.810205 0.104480 0.610352 0.591940
#2017-01-06 0.855625 0.808914 0.104311 0.610882 0.592583
#2017-01-07 0.857335 0.813948 0.104951 0.614860 0.593995
#2017-01-08 0.857330 0.813942 0.104951 0.614847 0.593990
#2017-01-09 0.864095 0.819833 0.105707 0.619112 0.600639
#2017-01-10 0.870830 0.822656 0.106077 0.622248 0.605533
#remove .GBP from all columns above
colnames(fxData) = gsub("[.]GBP","",colnames(fxData))
#set conversion factor for GBP = 1
fxData$GBP = 1
Data Reshape:
#create data.frame from xts data
fxData_DF = data.frame(date=index(fxData),coredata(fxData),stringsAsFactors=FALSE)
#To make fx dataset(wide format) amenable for merging with currDF
#we convert it to long format by using melt from reshape2 package see,?melt
fxMolten = melt(fxData_DF,id="date",variable.name="currency",value.name="conversionFactor")
head(fxMolten,2)
# date currency conversionFactor
#1 2017-01-01 EUR 0.85255
#2 2017-01-02 EUR 0.85255
tail(fxMolten,2)
# date currency conversionFactor
#59 2017-01-09 GBP 1
#60 2017-01-10 GBP 1
Merged Data:
#For each currency and date, we need a conversion factor hence
#we merge both datasets by columns date and currency
fxMerged = merge(currDF,fxMolten,by=c("date","currency"))
#calculate FX translated amount
fxMerged$convAmount = fxMerged$amount * fxMerged$conversionFactor
fxMerged
# date currency amount conversionFactor convAmount
#1 2017-01-01 EUR 1 0.852550 0.852550
#2 2017-01-02 GBP 2 1.000000 2.000000
#3 2017-01-03 USD 3 0.814326 2.442978
#4 2017-01-04 HKD 4 0.105102 0.420408
#5 2017-01-05 GBP 5 1.000000 5.000000
#6 2017-01-06 EUR 6 0.855625 5.133750
#7 2017-01-07 CAD 7 0.614860 4.304020
#8 2017-01-08 AUD 8 0.593990 4.751920
#9 2017-01-09 EUR 9 0.864095 7.776855
#10 2017-01-10 GBP 10 1.000000 10.000000
How do I get currency exchange rates via an API such as Google Finance?
Thanks for all your answers.
Free currencyconverterapi:
- Rates updated every 30 min
- API key is now required for the free server.
A sample conversion URL is: http://free.currencyconverterapi.com/api/v5/convert?q=EUR_USD&compact=y
For posterity here they are along with other possible answers:
Yahoo finance APIDiscontinued 2017-11-06###
Discontinued as of 2017-11-06 with message
It has come to our attention that this service is being used in
violation of the Yahoo Terms of Service. As such, the service is being
discontinued. For all future markets and equities data research,
please refer to finance.yahoo.com.
Request: http://finance.yahoo.com/d/quotes.csv?e=.csv&f=sl1d1t1&s=USDINR=X
This CSV was being used by a jQuery plugin called Curry. Curry has since (2017-08-29) moved to use fixer.io instead due to stability issues.
Might be useful if you need more than just a CSV.
- (thanks to Keyo) Yahoo Query Language lets you get a whole bunch of currencies at once in XML or JSON. The data updates by the second (whereas the European Central Bank has day old data), and stops in the weekend. Doesn't require any kind of sign up.
http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.xchange where pair in ("USDEUR", "USDJPY", "USDBGN", "USDCZK", "USDDKK", "USDGBP", "USDHUF", "USDLTL", "USDLVL", "USDPLN", "USDRON", "USDSEK", "USDCHF", "USDNOK", "USDHRK", "USDRUB", "USDTRY", "USDAUD", "USDBRL", "USDCAD", "USDCNY", "USDHKD", "USDIDR", "USDILS", "USDINR", "USDKRW", "USDMXN", "USDMYR", "USDNZD", "USDPHP", "USDSGD", "USDTHB", "USDZAR", "USDISK")&env=store://datatables.org/alltableswithkeys
Here is the YQL query builder, where you can test a query and copy the url: (NO LONGER AVAILABLE)
http://developer.yahoo.com/yql/console/?q=show%20tables&env=store://datatables.org/alltableswithkeys#h=select%20*%20from%20yahoo.finance.xchange%20where%20pair%20in%20%28%22USDMXN%22%2C%20%22USDCHF%22%29
Open Source Exchange Rates API
Free for personal use (1000 hits per month)
Changing "base" (from "USD") is not allowed in Free account
Requires registration.
Request: http://openexchangerates.org/latest.json
Response:
<!-- language: lang-js -->
{
"disclaimer": "This data is collected from various providers ...",
"license": "all code open-source under GPL v3 ...",
"timestamp": 1323115901,
"base": "USD",
"rates": {
"AED": 3.66999725,
"ALL": 102.09382091,
"ANG": 1.78992886,
// 115 more currency rates here ...
}
}
currencylayer API
Free Plan for 250 monthly hits
Changing "source" (from "USD") is not allowed in Free account
Requires registration.
Documentation: currencylayer.com/documentation
JSON Response:
<!-- language: lang-js -->
{
[...]
"timestamp": 1436284516,
"source": "USD",
"quotes": {
"USDAUD": 1.345352401,
"USDCAD": 1.27373397,
"USDCHF": 0.947845302,
"USDEUR": 0.91313905,
"USDGBP": 0.647603397,
// 168 world currencies
}
}
CurrencyFreaks API
Free Plan (1000 hits per month)
Changing 'Base' (From 'USD') is not allowed in free account
Requires registration
Data updated every 60 sec.
179 currencies worldwide including currencies, metals, and cryptocurrencies
Support (Even on the free plan) Shell,Node.js, Java, Python, PHP, Ruby, JS, C#, C, Go, Swift.
Documentation: https://currencyfreaks.com/documentation.html
Endpoint:
$ curl 'https://api.currencyfreaks.com/latest?apikey=YOUR_APIKEY'
JSON Response:
{
"date": "2020-10-08 12:29:00+00",
"base": "USD",
"rates": {
"FJD": "2.139",
"MXN": "21.36942",
"STD": "21031.906016",
"LVL": "0.656261",
"SCR": "18.106031",
"CDF": "1962.53482",
"BBD": "2.0",
"GTQ": "7.783265",
"CLP": "793.0",
"HNL": "24.625383",
"UGX": "3704.50271",
"ZAR": "16.577611",
"TND": "2.762",
"CUC": "1.000396",
"BSD": "1.0",
"SLL": "9809.999914",
"SDG": 55.325,
"IQD": "1194.293591",
.
.
.
[179 currencies]
}
}
Fixer.io API (European Central Bank data)
Free Plan for 1,000 monthly hits
Changing "source" (from "USD") is not allowed in Free account
Requires registration.
This API endpoint is deprecated and will stop working on June 1st, 2018. For more information please visit: https://github.com/fixerAPI/fixer#readme)
Example request :
[http://api.fixer.io/latest?base=USD][7]
Only collects one value per each day
European Central Bank Feed
Docs:
http://www.ecb.int/stats/exchange/eurofxref/html/index.en.html#dev
Request: http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml
XML Response:
<!-- language: lang-xml -->
<Cube>
<Cube time="2015-07-07">
<Cube currency="USD" rate="1.0931"/>
<Cube currency="JPY" rate="133.88"/>
<Cube currency="BGN" rate="1.9558"/>
<Cube currency="CZK" rate="27.100"/>
</Cube>
exchangeratesapi.io
According to the website:
Exchange rates API is a free service for current and historical foreign exchange rates published by the European Central BankThis service is compatible with fixer.io and is really easy to use:
For example (this uses CURL, but you can use your favorite requesting tool):
> curl https://api.exchangeratesapi.io/latest?base=GBP&symbols=USD&apikey=YOUR_KEY
{"base":"GBP","rates":{"USD":1.264494191},"date":"2019-05-29"}
CurrencyApi.net
Free Plan for 1250 monthly hits
150 Crypto and physical currencies - live updates
Base currency is set as USD on free account
Requires registration.
Documentation: currencyapi.net/documentation
JSON Response:
{
"valid": true,
"updated": 1567957373,
"base": "USD",
"rates": {
"AED": 3.673042,
"AFN": 77.529504,
"ALL": 109.410403,
// 165 currencies + some cryptos
}
}
Currency from LabStack
Website: https://labstack.com/currency
Documentation: https://labstack.com/docs/api/currency/convert
Pricing: https://labstack.com/pricing
Request: https://currency.labstack.com/api/v1/convert/1/USD/INR
Response:
```js
{
"time": "2019-10-09T21:15:00Z",
"amount": 71.1488
}
```
1: http://query.yahooapis.com/v1/public/yql?q=select * from yahoo.finance.xchange where pair in ("USDEUR", "USDJPY", "USDBGN", "USDCZK", "USDDKK", "USDGBP", "USDHUF", "USDLTL", "USDLVL", "USDPLN", "USDRON", "USDSEK", "USDCHF", "USDNOK", "USDHRK", "USDRUB", "USDTRY", "USDAUD", "USDBRL", "USDCAD", "USDCNY", "USDHKD", "USDIDR", "USDILS", "USDINR", "USDKRW", "USDMXN", "USDMYR", "USDNZD", "USDPHP", "USDSGD", "USDTHB", "USDZAR", "USDISK")&env=store://datatables.org/alltableswithkeys
currency-api
- Free & Blazing Fast response using CDN
- No Rate limits
- 150+ Currencies, Including Common Cryptocurrencies
- Daily Updated
Documentation: Link
Request: https://cdn.jsdelivr.net/gh/fawazahmed0/currency-api@1/latest/currencies/eur/jpy.json
Request(Fallback): https://cdn.jsdelivr.net/gh/fawazahmed0/currency-api@1/latest/currencies/eur/jpy.min.json
Response:
{
"date": "2021-10-03",
"jpy": 128.798673
}
How do I convert from one currency to another if only the country code is given?
We can use the countrycode package to convert ISO3 country codes into the relevant ISO4217 currency codes, then use priceR to use that to convert each currency to a single one, Euros in this example.
Let's first get the currency code.
library(countrycode)
library(priceR)
value$currency_code <- countrycode(value$Country, "iso3c", "iso4217c")
value
#> Country amount currency_code
#> 1 AUS 200 AUD
#> 2 AUT 150 EUR
#> 3 GBR 300 GBP
We can then get all the latest Euro exchange rates.
e_df <- exchange_rate_latest("EUR")
#> Daily EUR exchange rate as at end of day 2022-01-18 GMT
head(e_df)
#> currency one_eur_is_equivalent_to
#> 1 AED 4.184241
#> 2 AFN 119.337926
#> 3 ALL 122.074346
#> 4 AMD 547.591037
#> 5 ANG 2.052008
#> 6 AOA 611.091441
Last, let's bring that the exchange into our data frame and calculate the amounts in Euros.
value$exchange_euro <- e_df$one_eur_is_equivalent_to[match(value$currency_code, e_df$currency)]
value$amount_euro <- value$amount / value$exchange_euro
value
#> Country amount currency_code exchange_euro amount_euro
#> 1 AUS 200 AUD 1.584396 126.2311
#> 2 AUT 150 EUR 1.000000 150.0000
#> 3 GBR 300 GBP 0.836131 358.7955
Related Topics
How to Change the Background Color of the Shiny Dashboard Body
Manipulating Multiple Files in R
How to Split the Main Title of a Plot in 2 or More Lines
How to Replicate a Ddply Behavior That Uses a Custom Function with Dplyr
What's the Difference Between Hex Code (\X) and Unicode (\U) Chars
Get Selected Row from Datatable in Shiny App
How to Reduce Space Gap Between Multiple Graphs in R
How to Create a Continuous Density Heatmap of 2D Scatter Data in R
R - Common Title and Legend for Combined Plots
How to Get Currency Exchange Rates in R
How to Know If R Is Running on 64 Bits Versus 32
R "Stats" Citation for a Scientific Paper
Most Mature Sparse Matrix Package for R
How to Add Chapter Bibliographies Using Bookdown