R code snippet : Read Historical Daily Exchange Rates

This post shows how to read daily exchange rates given symbols as a string.


Read historical exchange rates


R code snippet : Read Historical Daily Exchange Rates
I collected the symbols of exchnage rates at

https://finance.yahoo.com/currencies


R code


The following R code retrieves historical daily exchange rates given their symbols as of 2022-08-14.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
#========================================================#
# Quantitative ALM, Financial Econometrics & Derivatives 
# ML/DL using R, Python, Tensorflow by Sang-Heon Lee 
#
# https://shleeai.blogspot.com
#--------------------------------------------------------#
# read historical exchange rates
#========================================================#
 
graphics.off(); rm(list = ls())
 
library(quantmod)
library(stringr) # trim
 
#-------------------------------------------------
# Symbols of exchange rates, as of 2022-08-14
#-------------------------------------------------
vstr_symbol <- "
    Symbol  ,    Name
    EURUSD=X,    EUR/USD   
    JPY=X   ,    USD/JPY  
    GBPUSD=X,    GBP/USD
    AUDUSD=X,    AUD/USD
    NZDUSD=X,    NZD/USD
    EURJPY=X,    EUR/JPY
    GBPJPY=X,    GBP/JPY
    EURGBP=X,    EUR/GBP
    EURCAD=X,    EUR/CAD
    EURSEK=X,    EUR/SEK
    EURCHF=X,    EUR/CHF
    EURHUF=X,    EUR/HUF
    CNY=X   ,    USD/CNY
    HKD=X   ,    USD/HKD
    SGD=X   ,    USD/SGD
    INR=X   ,    USD/INR
    MXN=X   ,    USD/MXN
    PHP=X   ,    USD/PHP
    IDR=X   ,    USD/IDR
    THB=X   ,    USD/THB
    MYR=X   ,    USD/MYR
    ZAR=X   ,    USD/ZAR
    RUB=X   ,    USD/RUB
    "
 
#-------------------------------------------
# split symbols and make vector
#-------------------------------------------
df <- read.table(text = str_trim(vstr_symbol), 
                 sep = ",", header = TRUE)
df <- as.data.frame(df); df
 
df$Symbol <- str_trim(gsub("[\t\r\n,]", "", df$Symbol))
df$Name   <- str_trim(gsub("[\t\r\n,]", "", df$Name))
df
nc <- nrow(df) # number of exchange rate
 
#-------------------------------------------
# read price information
#-------------------------------------------
sdate <- as.Date("2004-01-01")
edate <- as.Date("2022-07-31")
getSymbols(df$Symbol, from=sdate, to=edate)
 
#-------------------------------------------
# collect only adjusted prices
#-------------------------------------------
price <- NULL
for(i in 1:nc) {
    eval(parse(text=paste0(
        "price <- cbind(price,`",
        gsub("\\^","",df$Symbol[i]),"`[,6])")))
}
 
# modify column Name as only symbol
colnames(price) <- gsub(".X.Adjusted", "", colnames(price))
 
# convert to data.frame with the first column as Date
df.price <- cbind(time=time(price), as.data.frame(price))
rownames(df.price) <- NULL
 
#-------------------------------------------
# print time series of daily prices
#-------------------------------------------
head(df.price,3)
tail(df.price,3)
 
cs


Running the above R code displays the status of data reading process as follows.
R code snippet : Read Historical Daily Exchange Rates

Finally, we can get the collection of individual exchange rates.
R code snippet : Read Historical Daily Exchange Rates

4 comments:

  1. UnknownOctober 6, 2022 at 9:21 AM

    I am interested in the code above, but i have doubt:

    q1)

    at line 70

    "price <- cbind(price,`",
    gsub("\\^","",df$Symbol[i]),"`[,6])")))

    What does the symbol (the grave accent, near the end) do there (sorry, this Comment section was not able to show the symbol correctly)

    same question, what does the symbol (the grave accent) do there? And I tried changing parameter [,6] to other number, such as [,7], it would say out of bound...
    changing it to [,2] it would accept with the output similarly correct...cannot figure out what it affects.

    Thanks

    ReplyDelete
    Replies
    1. Sang-Heon LeeOctober 6, 2022 at 11:32 AM

      A1) In this case of the exchange rates, you can use

      "price <- cbind(price,`",df$Symbol[i],"`[,6])"))

      for example, in case of JPY
      > i=2
      > df$Symbol[i]
      [1] "JPY=X"
      > gsub("\\^","",df$Symbol[i])
      [1] "JPY=X"

      I just used some generic expression for the symbols. Stock symbols include ^ like ^GSPC. This is the symbol of S&P 500 index but it is used as GSPC in R with ^ deleted. So I use gsub() function to delete it.

      A2) yahoo financial information consists of 6 columns : Open Price, High Price, Low Price, Close Price, Volume, Adjusted Price.

      [,6] means that the adjusted price is selected. Therefore the 7th column does not exist.

      for example, you can type the following R command to see all columns of it.

      > `JPY=X`
      JPY=X.Open JPY=X.High JPY=X.Low JPY=X.Close JPY=X.Volume JPY=X.Adjusted
      2004-01-01 106.950 107.4700 106.9500 107.300 0 107.300
      2004-01-02 107.320 107.4900 106.7300 106.910 0 106.910

      Good luck.

      Delete
    2. UnknownOctober 6, 2022 at 7:13 PM

      Thank you for your reply, i understand now better the lines 69 to 71 , all that eval(parse(paste0 ....))

      it is frustrating that this Comment section is not able to show correctly that backticks (in the code) , when you said:
      > JPY=X
      I was dumbfounded, at last i figure out i need to put the backticks quoting that command to get it works,

      I wonder is it (the backticks) a feature/command coming from R's zoo/xts library?

      I mean:
      > (backtick)JPY=X(backtick) works only after executing all the lines from beginning till the line 62, getSymbols(), which is belonged to Quantmod library.

      Sorry if it is a dumb question, I was not able to find other example in Googling.

      Delete
    3. Sang-Heon LeeOctober 7, 2022 at 9:26 AM

      In case of getSymbols(), I don't know where the backticks come from. I just take it for granted that the backticks should be used when a symbol contains "=" or "-", etc. since these characters cannot be used as a variable name in R.

      Delete

Subscribe to: Post Comments (Atom)