[R] Help needed for getYahooData in TTR package & writing the Yahoo data to excel

David Winsemius dwinsemius at comcast.net
Sun Oct 10 14:09:31 CEST 2010 

On Oct 10, 2010, at 5:27 AM, vanilla fantasy wrote:

> Just trying to be clearer on the problem faced, the dates in the 1st  
> colume become 1,2,3,4 in excel as below, the screenshot of how data  
> appears in excel is attached.

I can affirm that there was at one time a jpg since you copied me an  
my mail client is much less suspicious about attachments than is the  
mainly lis server. Nobody else got a copy, though.

>
>               Open      High         Low       Close      Volume
> 1          18937.45 19187.61 18937.45 19002.86      0
> 2          19003.51 19003.51 18221.82 18542.55      0
> 3          18574.01 18582.74 18168.27 18168.27      0
> 4          18194.05 18285.73 18068.10 18193.41      0
> 5          18246.10 18887.56 18246.10 18850.92      0

THat was really not a data.frame in R, but rahter an xts object and  
when write.table coverted it to a data.frame the dates (which were an  
attribute got stripped off.:

 > str(N225)
An ‘xts’ object from 2000-01-04 to 2010-10-08 containing:
   Data: num [1:2644, 1:5] 18937 19004 18574 18194 18246 ...
  - attr(*, "dimnames")=List of 2
   ..$ : NULL
   ..$ : chr [1:5] "Open" "High" "Low" "Close" ...
   Indexed by objects of class: [POSIXt,POSIXct] TZ:
   xts Attributes:
  NULL

 > str(as.data.frame(N225))
'data.frame':	2644 obs. of  5 variables:
  $ Open  : num  18937 19004 18574 18194 18246 ...
  $ High  : num  19188 19004 18583 18286 18888 ...
  $ Low   : num  18937 18222 18168 18068 18246 ...
  $ Close : num  19003 18543 18168 18193 18851 ...
  $ Volume: num  0 0 0 0 0 0 0 0 0 0 ...

You probably first need to extract the dates from that xts object
 > names(attributes(N225))
[1] "index"       "dim"         "dimnames"    "class"        
".indexCLASS"
[6] ".indexTZ"
 > str(attr(N225, "index"))
  num [1:2644] 9.47e+08 9.47e+08 9.47e+08 9.47e+08 9.48e+08 ...

So the dates are not in a DateTime format, but experimentation shows  
them to be a series of 5:00:: if treated as POSIXct.
 > dts <- as.POSIXct(attributes(N225)$index, origin="1970-01-01")
 > str(dts)
  POSIXct[1:2644], format: "2000-01-04 05:00:00" "2000-01-05  
05:00:00" ...

You will first need to use as.data.frame to "re-class" the data matrix  
in N225 and then add a column of dates. Sorry for the initial off-base  
reply. I should have looked at the tab separated file you created  
rather than assuming I what would happen. Maybe all in one stroke:

 > N225df <- cbind(dts, as.data.frame(N225) )
 > str(N225df)
'data.frame':	2644 obs. of  6 variables:
  $ dts   : POSIXct, format: "2000-01-04 05:00:00" "2000-01-05  
05:00:00" ...
  $ Open  : num  18937 19004 18574 18194 18246 ...
  $ High  : num  19188 19004 18583 18286 18888 ...
  $ Low   : num  18937 18222 18168 18068 18246 ...
  $ Close : num  19003 18543 18168 18193 18851 ...
  $ Volume: num  0 0 0 0 0 0 0 0 0 0 ...


-- 
David.

> .
> .
>
> On Sun, Oct 10, 2010 at 11:55 AM, David Winsemius <dwinsemius at comcast.net 
> > wrote:
>
> On Oct 9, 2010, at 10:54 PM, missvanilla wrote:
>
>
> Dear all,
>
> I'm totally new to R. Recently I've been trying to use getYahooData  
> in TTR
> package in order to download stock index daily open/high/low/close.  
> The
> downloaded data is in the format of
>
>                  Open      High         Low       Close      Volume
> 2000-01-04 18937.45 19187.61 18937.45 19002.86      0
> 2000-01-05 19003.51 19003.51 18221.82 18542.55      0
> 2000-01-06 18574.01 18582.74 18168.27 18168.27      0
> 2000-01-07 18194.05 18285.73 18068.10 18193.41      0
> 2000-01-11 18246.10 18887.56 18246.10 18850.92      0
> 2000-01-12 18780.17 18811.87 18626.92 18677.42      0
> 2000-01-13 18667.18 18845.03 18667.18 18833.29      0
> 2000-01-14 18882.99 19058.02 18733.83 18956.55      0
> 2000-01-17 19025.62 19442.58 19025.62 19437.23      0
> 2000-01-18 19412.47 19412.47 19145.17 19196.57      0
>
> However, when I attempted to write the data to excel using  
> write.table,
> dates in the first colume  become 1,2,3,4 in the excel file. Same  
> problem
> happened if write.csv was used.
>
> If you run these two lines of code you'll get what I meant.. before  
> running
> the code, package TTR needs to be loaded.
>
> N225 <- getYahooData("^N225", 20000101, )
> write.table(N225,"Nikkei.xls",sep='\t', row.name = TRUE , col.name =  
> NA)
>
> There is a well-described problem with write.table files going into  
> Excel. There is no leading item or tab on the first row. You need to  
> insert an extra cell and move the header over one position. Then you  
> won't be misinterpreting your row.names as dates.
>
> -- 
> David
>
>
> Appreciate your kind assistance! Thanks a lot in advance.
>
> -- 
> View this message in context: http://r.789695.n4.nabble.com/Help-needed-for-getYahooData-in-TTR-package-writing-the-Yahoo-data-to-excel-tp2970017p2970017.html
> Sent from the R help mailing list archive at Nabble.com.
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
>
> <nikkei screenshot.jpg>

David Winsemius, MD
West Hartford, CT

More information about the R-help mailing list