2 Messages
Digest #3470
Messages
Sun Jul 26, 2015 11:16 am (PDT) . Posted by:
cancerfixer
I have a backtest spreadsheet in Excel 2010 that uses RCHGetYahooHistory to populate historical prices. For various reasons I prefer to get the data for each security on a scratch page, copy it to another sheet, then erase the scratch page & repeat for a different ticker. I use the following command to write the array formula onto the scratch page:
Range("A2:G2001").FormulaArray = "=RCHGetYahooHistory(" & Chr(34) & sTicker & Chr(34) & "," & _
sStartYear & "," & sStartMo & "," & sStartDay & "," & _
sEndYear & "," & sEndMo & "," & sEndDay & "," & _
Chr(34) & "m" & Chr(34) & "," & _
Chr(34) & "DA" & Chr(34) & "," & _
"1," & _
"0," & _
"1)"
Because the size of the array varies depending on the dates selected, I use a supersize range that is more than large enough to accommodate the date range.
On this particular run, the formula translated from the variables is:
"=RCHGetYahooHistory("VFINX",2000,3,1,2006,9,31,"m","DA",1,0,1)"
so I am requesting monthly adjusted quotes for VFINX from 3/1/2000 to 9/31/2006 and sorted from earliest to latest date.
This works great, as noted on the attached image showing the result. Note that the proper array formula is inside every cell. But there is one problem: You will note that on this run the end date is 9/31/2006. Despite the correct date in the formula and the correct date seen inside every cell, the function insists on returning ALL dates up until the present. Huh? I thought it was supposed to stop at the end date.
I have tried to force recalculation, but that has no effect, as expected.
Unless I can figure this problem out I'll be forced to search the scratch page for the desired end date and only copy up to that point. I'd prefer to have the function only return the requested data confined to the date range.
What am I doing wrong?
Range("
sStartYear & "," & sStartMo & "," & sStartDay & "," & _
sEndYear & "," & sEndMo & "," & sEndDay & "," & _
Chr(34) & "m" & Chr(34) & "," & _
Chr(34) & "DA" & Chr(34) & "," & _
"1," & _
"0," & _
"1)"
Because the size of the array varies depending on the dates selected, I use a supersize range that is more than large enough to accommodate the date range.
On this particular run, the formula translated from the variables is:
"=RCHGetYahooH
so I am requesting monthly adjusted quotes for VFINX from 3/1/2000 to 9/31/2006 and sorted from earliest to latest date.
This works great, as noted on the attached image showing the result. Note that the proper array formula is inside every cell. But there is one problem: You will note that on this run the end date is 9/31/2006. Despite the correct date in the formula and the correct date seen inside every cell, the function insists on returning ALL dates up until the present. Huh? I thought it was supposed to stop at the end date.
I have tried to force recalculation, but that has no effect, as expected.
Unless I can figure this problem out I'll be forced to search the scratch page for the desired end date and only copy up to that point. I'd prefer to have the function only return the requested data confined to the date range.
What am I doing wrong?
Attachment(s) from
1 of 1 Photo(s)
Sun Jul 26, 2015 12:34 pm (PDT) . Posted by:
cancerfixer
Aha! I found the error. The formula selects an end date of 9/31/06. This is not a valid date, since September only has 30 days. Apparently yahoo finds an invalid end date, so defaults to no end date at all. Smart yahoo!
If you enter the same formula with 9/30/06 as an end date it works as intended...
Thanks for being my sounding board!
If you enter the same formula with 9/30/06 as an end date it works as intended...
Thanks for being my sounding board!
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar