Preserving Dates as a Column in Quantmod Price Data
Summary
The document explains why historical price data returned by R’s quantmod and tseries download functions retain dates as an index rather than as a regular column. The returned objects are matrices, which can hold only one data type; mixing a Date value with numeric prices in the same matrix would therefore require coercion. The suggested approach converts the downloaded object to a data frame, then copies its time index into a new date column. The examples cover both getSymbols and get.hist.quote.
This is a practical data-handling note for preparing market data for workflows that expect dates as ordinary fields. It demonstrates the method with equity price history, but provides no comparison of formats, performance evidence, or discussion of downstream use. The key limitation is that the conversion changes the data structure: users should select a suitable table format when a date column is required, while retaining the indexed object if its time-series behavior is needed.
Key ideas
- Downloaded price objects use dates as an index and store numeric observations in a matrix.
- A matrix cannot hold numeric prices and Date-class values as distinct column types.
- Convert the result to a data frame before adding the time index as a date column.
- The same conversion pattern applies to both getSymbols and get.hist.quote results.
Tags
Full text
# I want to keep a column in getSymbols or get.hist.quote with the Date as as.Date format
# I want to keep a column in getSymbols or get.hist.quote with the Date as as.Date format
I use `getSymbols` to download historical information over an environment with 200 tickers but I cannot seem to keep the date information from becoming an index. I tried to specify `return.class` to `"ts"` and `"zoo"` also but no luck...
I also used `get.hist.quote` with similar success...
BUT the only thing that works is
```
xom <- read.csv("http://ichart.yahoo.com/table.csv?s=xom&a=11&b=31&c=2012", stringsAsFactors=F)
# The first column contains dates. The as.Date
# function can convert strings into Date objects.
#
xom_dates <- as.Date(xom[,1])
```
is there ANY way to preferably use the getSymbols to format one column with the dates as as.Date as above?? Help
## Answer by Sven Hohenstein (score 2)
https://quant.stackexchange.com/a/10271
You cannot add a date column to an object returned by `getSymbols` or `get.hist.quote`. These function return matrices. Matrices can only store data of the same type, in this case the matrices contain `double` values (real numbers).
You can add a column of class `Date` to the objects if you transform them into a data frame:
For `getSymbols`:
```
library(quantmod)
getSymbols('IBM', from = "2014-02-10")
IBM
# IBM.Open IBM.High IBM.Low IBM.Close IBM.Volume IBM.Adjusted
# 2014-02-10 176.97 177.65 176.25 177.14 3540400 177.14
# 2014-02-11 176.81 180.39 176.80 179.70 4647300 179.70
# 2014-02-12 179.52 181.25 179.26 180.24 3983000 180.24
# 2014-02-13 178.22 182.36 177.86 181.84 4482000 181.84
# 2014-02-14 181.26 184.43 180.99 183.69 4659900 183.69
dat1 <- as.data.frame(IBM)
dat1$date <- time(IBM)
dat1
# IBM.Open IBM.High IBM.Low IBM.Close IBM.Volume date
# 2014-02-10 176.97 177.65 176.25 177.14 3540400 2014-02-10
# 2014-02-11 176.81 180.39 176.80 179.70 4647300 2014-02-11
# 2014-02-12 179.52 181.25 179.26 180.24 3983000 2014-02-12
# 2014-02-13 178.22 182.36 177.86 181.84 4482000 2014-02-13
# 2014-02-14 181.26 184.43 180.99 183.69 4659900 2014-02-14
```
The approach is similar for `get.hist.quote`:
```
library(tseries)
res <- get.hist.quote(instrument = "ibm", start = "2014-02-10")
res
# Open High Low Close
# 2014-02-10 176.97 177.65 176.25 177.14
# 2014-02-11 176.81 180.39 176.80 179.70
# 2014-02-12 179.52 181.25 179.26 180.24
# 2014-02-13 178.22 182.36 177.86 181.84
# 2014-02-14 181.26 184.43 180.99 183.69
dat2 <- as.data.frame(res)
dat2$date <- time(res)
dat2
# Open High Low Close date
# 2014-02-10 176.97 177.65 176.25 177.14 2014-02-10
# 2014-02-11 176.81 180.39 176.80 179.70 2014-02-11
# 2014-02-12 179.52 181.25 179.26 180.24 2014-02-12
# 2014-02-13 178.22 182.36 177.86 181.84 2014-02-13
# 2014-02-14 181.26 184.43 180.99 183.69 2014-02-14
```Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.