Converting an excel file into an xts format

General
1

Hi Guys,

I have an open-high-low-close price time series of 15-min frequency. The file format is in Excel. As I am a complete novice in R, I do not have a clue how to convert the Excel file into an xts format. I have tried as.xts(read.table("SSS")), but with no success. I would really appreciate if you could help me out with a simple code for this conversion. Thank you.

reprex()

library(xts)
as.xts(read.table("QQQ"))
Error in file(file, "rt") : cannot open the connection
In addition: Warning message:
In file(file, "rt") : cannot open file 'QQQ': No such file or directory

date o h l c
10/05/2016 00:00 461.49 462.06 461.03 461.28
10/05/2016 00:15 461.3 461.66 460.37 461.66
10/05/2016 00:30 461.56 461.56 460.01 460.53
10/05/2016 00:45 460.21 461.33 460.03 460.49
10/05/2016 01:00 461.6 462.69 460 462.24
10/05/2016 01:15 461.54 463 461.53 462
10/05/2016 01:30 462.94 462.99 461.63 462.62
10/05/2016 01:45 462.62 462.62 461.05 461.14
10/05/2016 02:00 461.91 461.91 461.05 461.81
10/05/2016 02:15 461.09 462.19 461.01 461.33
10/05/2016 02:30 461.92 461.92 460 460.23
10/05/2016 02:45 460.93 461 460.01 460.63
10/05/2016 03:00 460.63 460.85 460 460
10/05/2016 03:15 460 460.61 459.5 459.97
10/05/2016 03:30 459.68 460.93 459.68 460.8
10/05/2016 03:45 460.15 461.26 460.02 460.68
10/05/2016 04:00 460.93 461.01 460.12 460.13
10/05/2016 04:15 460.13 460.74 459.91 460.01
10/05/2016 04:30 460.01 460.77 460 460.71
10/05/2016 04:45 460.08 460.79 459.96 460.79
10/05/2016 05:00 460.09 460.9 460.06 460.17
10/05/2016 05:15 460.9 461.65 460.19 460.21
10/05/2016 05:30 460.26 461.36 460.07 460.99
10/05/2016 05:45 460.8 461.38 460 461
10/05/2016 06:00 460.5 461.2 460.08 460.9
10/05/2016 06:15 460.9 460.98 460.1 460.12
10/05/2016 06:30 460.12 460.12 459.6 459.6
10/05/2016 06:45 459.6 459.88 458.61 459.66
10/05/2016 07:00 459 460.88 459 459.83
10/05/2016 07:15 459.83 460.9 459.81 460.9
10/05/2016 07:30 460.26 461.36 459.91 461.3
10/05/2016 07:45 460.44 461.38 460.01 460.76
10/05/2016 08:00 460.7 460.86 459.61 460.17
10/05/2016 08:15 460.17 460.92 459.05 460.35
10/05/2016 08:30 460.68 460.88 459.3 460.88
10/05/2016 08:45 460.72 460.72 458.66 459
10/05/2016 09:00 459 459.6 457.28 457.28
10/05/2016 09:15 458.61 458.61 456.12 458.43
10/05/2016 09:30 457 457.86 455.48 455.48
10/05/2016 09:45 455.85 456.06 453.1 454.88
10/05/2016 10:00 454.21 456.13 454.21 455.65
10/05/2016 10:15 455.45 455.45 450.9 452.99
10/05/2016 10:30 451.96 452.99 447.38 450.6
10/05/2016 10:45 450.68 452.08 450.11 451.99
10/05/2016 11:00 452 452 450.21 451.5
10/05/2016 11:15 451.5 452.45 450.68 452.45
10/05/2016 11:30 452.5 453.17 450.99 452.96
10/05/2016 11:45 451.54 454.14 451.22 453.21
10/05/2016 12:00 453.07 454.41 452.16 453.34
10/05/2016 12:15 453.26 454.5 452.1 453.35
10/05/2016 12:30 453.34 453.35 452.17 452.19
10/05/2016 12:45 453.21 453.35 452.02 453.19
10/05/2016 13:00 453.13 454.49 452.6 454.44
2

Convert your Excel file to a data.frame with e.g. openxlsx::read.xlsx
and then use xts::as.xts.data.frame to convert it to an xts object.

3

Many thanks HanOostdijk for your help. Actually, your suggestion provided me with a partial solution. I succeeded in converting the xlsx file into a data.frame one, using the following
my_data <- read_excel("QQQ.xlsx")

However, when applying

as.xts(read.table (my_data))
I got the following error

Error in read.table(my_data) :
'file' must be a character string or connection.

Any help would be appreciated

4

You don't need to read it in again. It would be like:

library(openxlsx)
library(xts)
my_data <- read_excel("QQQ.xlsx")
my_xts <- as.xts((my_data)
5

Thanks a lot Stephanie for your help. Unfortunately, it does not work successfully. I got the following error

my_data <- read_excel("QQQ.xlsx")
my_xts <- as.xts((my_data))
Error in as.POSIXlt.character(x, tz, ...) :
character string is not in a standard unambiguous format

6

If you have the possibility to indicate which column contains the date please do so in as.xts.data.frame.
And be sure your date column has indeed the right format: given the error probably not.
And if it is not the right format adjust the date with the lubridate package.

1 Like
7

Cheers, it works for me. Thanks a lot.

8

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.