Importing text files

Importing and Managing Financial Data in R

Joshua Ulrich

Quantitative Analyst & quantmod Co-Author and Maintainer

getSymbols() with CSV files

Well-formatted
- One instrument per file
- Columns: date, open, high, low, close, volume, adjusted close
Files named "[symbol].csv"
Can use dir argument to specify directory

getSymbols() with CSV files

AMZN.csv

"Date","AMZN.Open","AMZN.High","AMZN.Low","AMZN.Close","AMZN.Volume","AMZN.Adjusted"
2002-01-02,11.13,11.01,10.46,10.87,6674703,10.87
2002-01-03,11.26,12.25,10.76,11.99,11441553,11.99
2002-01-04,12.46,12.62,11.71,12.1,12619402,12.1

getSymbols("AMZN", src = "csv")

"AMZN"

head(AMZN, 3)

           AMZN.Open AMZN.High AMZN.Low AMZN.Close AMZN.Volume AMZN.Adjusted
2002-01-02     11.13     11.01    10.46      10.87     6674703         10.87
2002-01-03     11.26     12.25    10.76      11.99    11441553         11.99
2002-01-04     12.46     12.62    11.71      12.10    12619402         12.10

read.zoo()

AMZN.csv

"Date","AMZN.Open","AMZN.High","AMZN.Low","AMZN.Close","AMZN.Volume","AMZN.Adjusted"
2002-01-02,11.13,11.01,10.46,10.87,6674703,10.87
2002-01-03,11.26,12.25,10.76,11.99,11441553,11.99
2002-01-04,12.46,12.62,11.71,12.1,12619402,12.1

amzn_zoo <- read.zoo("AMZN.csv", sep = ",", header = TRUE)
amzn_xts <- as.xts(amzn_zoo)
head(amzn_xts, n = 3)

           AMZN.Open AMZN.High AMZN.Low AMZN.Close AMZN.Volume AMZN.Adjusted
2002-01-02     11.13     11.01    10.46      10.87     6674703         10.87
2002-01-03     11.26     12.25    10.76      11.99    11441553         11.99
2002-01-04     12.46     12.62    11.71      12.10    12619402         12.10

Date and time in separate columns

FOO.csv

"Date","Time","Open","High","Low","Close"
2016-11-08,09:05:00,80.9,81,80.87,81
2016-11-08,09:10:00,80.92,80.93,80.89,80.89
2016-11-08,09:15:00,80.93,80.94,80.92,80.93

foo_zoo <- read.zoo("FOO.csv", sep = ",", header = TRUE,      
                      index.column = c("Date", "Time"))
head(foo_zoo,  n = 3)

                     Open  High   Low Close
2016-11-08 09:05:00 80.90 81.00 80.87 81.00
2016-11-08 09:10:00 80.92 80.93 80.89 80.89
2016-11-08 09:15:00 80.93 80.94 80.92 80.93

File contains multiple instruments

BAR.csv

Date,Symbol,Type,Price
2016-01-01 10:43:01,A,Bid,58.23
2016-01-01 10:43:01,A,Ask,58.24
2016-01-01 10:43:01,B,Bid,28.96
2016-01-01 10:43:01,B,Ask,28.98

bar_zoo <- read.zoo("BAR.csv",
                    split = c("Symbol", "Type"),
                    sep = ",", header = TRUE)
bar_zoo

                    A.Ask B.Ask A.Bid B.Bid
2016-01-01 10:43:01 58.24 28.98 58.23 28.96
2016-01-01 10:43:02 58.25 28.99 58.24 28.97

Let's practice!

Importing and Managing Financial Data in R