\(~~~~~~~~\)Data Structure in R: XTS\(~~~~~~~~\)

Somsak Chanaim

International College of Digital Innovation, CMU

October 29, 2025

Warning

We cannot use the quantmod package to download data from Yahoo Finance in WebR.

Yahoo Finance has restricted public API access, and WebR does not allow direct internet connections.

Therefore, we use synthetic OHLC data generated within R to demonstrate xts and time-series operations.

Why we need quantmod

Download daily price of AAPL from Yahoo finance, from \(~~\)1 Jan 2020 to 30 Sep 2024, and upload to Rstudio and make this plot

Quantitative Financial Modelling Framework (quantmod)

The quantmod is useful for quantitative financial modelling framework and have three goals:

  • download data

  • charting

  • technical indicator

install.packages("quantmod")
library(quantmod)

Download Data

getSymbols() function

getSymbols: The function for load and manage data from multiple source

Recommend the arguments

  • Symbols : vector of stock or asset name

  • src : “yahoo”, FRED” , etc (this course use yahoo)

  • periodicity : “daily”, “weekly”, or monthly”

  • from : Starting day: Year-month-day (2020-11-31)

  • to : Ending day: Year-month-day (2022-12-31)

The object from yahoo finance is XTS.

Example

o show that we cannot download data from Yahoo Finance using WebR.

or download multiple stocks price

The xts package

eXtensible Time Series (xts)

  • eXtensible Time Series (xts) is a powerful package that provides an extensible time series class, enabling uniform handling of many R time series classes by extending zoo.

  • The main benefit of using xts is the compatibility with other packages that use different time-series classes (timeSeries, zoo, …).

Selecting, Subsetting and Indexing

We have 3 xts objects, AAPL, META, and MSFT

Data Frame command

Warning

In this study, We don’t need the variables AAPL.Volume and AAPL.Adjusted

Selecting data by year

Selecting data by year and month

Selecting data from starting date to any date

Selecting data from any date to last date

Selecting data for the first n weeks

Selecting data for the first n days

Selecting data for the last n weeks

Selecting data for the last n days

From daily price to weekly price

From daily price to monthly price

From daily price to monthly price

How to export XTS object to xlsx file

Case 1: Export only one xts object to an .xlsx file.

Step 1: Convert the xts object to a data frame.

DF_AAPL <- AAPL |> 
                 coredata() |> 
                 as.data.frame()
DF_AAPL$Date <- index(AAPL)

  • coredata() extracts the data values from the xts object, excluding the time index.

  • as.data.frame() converts the extracted data into a standard data frame.

  • DF_AAPL$Date <- index(AAPL) adds a new column called Date, containing the time index from the original xts object.

Step 2: Save data frame DF_AAP to xlsx

library(writexl)
write_xlsx(DF_AAPL, "AAPL.xlsx")

Case 2: Export multiple xts objects to an .xlsx file.

Step 1: Convert the xts object to a data frame.

DF_MSFT <- MSFT |> 
                 coredata() |> 
                 as.data.frame()
DF_MSFT$Date <- index(MSFT)
DF_META <- META |> 
                 coredata() |> 
                 as.data.frame()
DF_META$Date <- index(META)

Step 2: Save every data frame to xlsx

library(writexl)
write_xlsx(list(DF_AAPL, DF_META, DF_MSFT), "stock.xlsx")

How to Import Time Series Data from an XLSX File into an XTS Object in R

You can download financial time series data from my webapp.

Assume we have excel file file.xlsx

tibble [968 × 7] (S3: tbl_df/tbl/data.frame)
 $ date           : POSIXct[1:968], format: "2021-01-04" "2021-01-05" ...
 $ PTT.BK.Open    : num [1:968] 41 42.2 43 43 42.8 ...
 $ PTT.BK.High    : num [1:968] 42.8 42.5 43.5 43.2 43.5 ...
 $ PTT.BK.Low     : num [1:968] 40.8 41.2 42.5 42.2 42 ...
 $ PTT.BK.Close   : num [1:968] 42.5 42 42.8 42.5 42.8 ...
 $ PTT.BK.Volume  : num [1:968] 74638600 75867500 88565400 63128900 83698400 ...
 $ PTT.BK.Adjusted: num [1:968] 33 32.6 33.1 33 33.1 ...

Convert the data frame TS to an XTS object using this function.

           PTT.BK.Open PTT.BK.High PTT.BK.Low PTT.BK.Close PTT.BK.Volume
2021-01-04       41.00       42.75      40.75        42.50      74638600
2021-01-05       42.25       42.50      41.25        42.00      75867500
2021-01-06       43.00       43.50      42.50        42.75      88565400
2021-01-07       43.00       43.25      42.25        42.50      63128900
2021-01-08       42.75       43.50      42.00        42.75      83698400
2021-01-11       42.75       43.00      42.50        43.00      45553200
2021-01-12       43.00       44.00      42.50        43.75     101081900

Exercise

Exercise 1: Extract Data for a Specific Year

Extract and display the stock data for “MSFT” for the year 2021. Use the xts package’s subsetting techniques to filter by date.

Solution:

   msft_2021 <- MSFT["2021"]
   head(msft_2021)

Exercise 2: Filter Data Between Two Dates

Subset the “META” data to include only the observations between January 1, 2022, and December 31, 2022.

Solution:

   meta_2022 <- META["2022-01-01/2022-12-31"]
   head(meta_2022)

Exercise 3: Select Data for the First Quarter

Extract the data for “MSFT” for the first quarter of 2023 (January to March) and display the results.

Solution:

   msft_q1_2023 <- MSFT["2023-01/2023-03"]
   head(msft_q1_2023)

Exercise 4: Subset Data for the First N Weeks

Create a subset of “META” data for the first 10 weeks of 2024 and calculate the average closing price during that period.

Solution:

mean(META$META.Close["2024"])

Exercise 5: Compare Data Over Two Periods

Extract “MSFT” data for the first 6 months of 2021 and 2023, and compare the average closing prices for these two periods.

Solution:

   avg_2021 <- mean(MSFT$MSFT.Close["2021-01/2021-06"])
   avg_2023 <- mean(MSFT$MSFT.Close["2023-01/2023-06"])
   c(avg_2021, avg_2023)

Exercise 6: Select the Last N Observations

Subset the “META” data to get the last 8 weeks of observations before September 30, 2024.

Solution:

tail(META["/2024-09-30"], n=8)