RCHGetYahooHistory errors

[ClimberMel]

The formula RCHGetYahooHistory(C7,C8,C9,C10,C11,C12,C13,C14,C15,C16,C17,C18) is array entered and showing #NAME in all cells.
The data for C7 to C18 for the example is as follows: aapl,1990,1,1,2025,1,1,w,,1,1,1
The fields for those are: Ticker:,Start Year:,Start Month:,Start Day:,End Year:,End Month:,End Day:,Period:,Items:,Header:,Adjust:,Resort:

[ClimberMel]

I have traced the numerous modules that it goes through. The smfGetYahooHistory() is the problem module.

[aggie81]

Hi, I am not much of a VBA programmer other than simple macros, but I am an engineer that does embedded firmware coding and can do a bit of debugging and testing. I stepped through the smfGetYahooHistory() code and it seems the current code is looking for the string "HistoricalPriceStore" in the web page data returned via sData = smfGetWebPage(sURL). It seems this string is no longer in the returned web data source. Next step would be to capture the curent web page source to locate a new string that would allow smfStrExtr to find and return the stock data JSON it is looking for. Unfortunately I am not a HTML programmer either, so not sure how to do this, but am googling it and willing to learn. Thanks for your efforts to keep SMF_Add-in going!

[ClimberMel]

In the function smfGetYahooHistory() it does a bunch of formatting and checking parameters… that all looks fine.
Then it get to the section:

'------------------> Create URL and retrieve data
sURL = "https://finance.yahoo.com/quote/" & pTicker & "/history?period1=" & dBegin & "&period2=" & dEnd & _
       "&interval=" & sInterval & "&filter=" & sfilter & "&frequency=" & sFreq
'sData = RCHGetURLData(sURL)
sData = smfGetWebPage(sURL)
sData = smfStrExtr(sData, "HistoricalPriceStore", "]")   ' Keep only the "HistoricalPriceStore" JSON data
vByDay = Split(sData, "},{")

The URL seems fine, it becomes this: https://finance.yahoo.com/quote/msft/history?period1=1641081600&period2=1672444800&interval=1d&filter=history&frequency=1d
sData = smfStrExtr is where the problem is, the comment indicates it was looking for json data and the “HistoricalPriceStore" no longer exits.
There is a data-test="historical-prices" in the HTML, but I’m having trouble figuring out how to extract the data following that.
The vByDay = Split(sData, "},{") is also looking to split jason data based on the “},{“, but there is no json data here.

Thanks,
Mel

sample_html.txt

[aggie81]

I see the same thing. Using Firefox and its code inspector tool, I grabbed two screen shots. One shows the start of the table marked by data-test="historical-prices" that you found. The second shows the first table entry (date, open, high, etc. data). You can see the data values in there but seems no longer in a simple JSON format.

[ClimberMel]

I have been mapping the modules and calls, it has been quite a lengthy process. I'm also slowly learning more about scaping data. I think now that I have the block of data I need, I should be able to figure out how the module parses that data and re-write it accordingly.

[ClimberMel]

I have the function smfGetYahooHistory in module smfGetYahooHistory_ working with returned data. It can again select which items / columns and the order in which to return the data.
I will test with returning the RCHGetYahooHistory to the way it was since the fix to it was to bypass the failing smfGetYahooHistory function.

[aggie81]

Sweet! Eager to give it a try! Has the SMF download page been updated? Thanks!

[ClimberMel]

Yes it is updated. There seems to be still a few things to work on, but getting there. J Mel From: aggie81 ***@***.*** Sent: Tuesday, February 07, 2023 4:31 PM To: ClimberMel/SMF_Add-in Cc: ClimberMel; Assign Subject: Re: [ClimberMel/SMF_Add-in] RCHGetYahooHistory errors (Issue #2) Sweet! Eager to give it a try! Has the SMF download page been updated? Thanks! — Reply to this email directly, view it on GitHub <#2 (comment)> , or unsubscribe <https://github.com/notifications/unsubscribe-auth/ACMB2YORFUDCSEEJRFVYAJLWWLSNLANCNFSM6AAAAAATXGKWGQ> . You are receiving this because you were assigned. <https://github.com/notifications/beacon/ACMB2YIGOTV6Y2ITFNRFUC3WWLSNLA5CNFSM6AAAAAATXGKWGSWGG33NNVSW45C7OR4XAZNMJFZXG5LFINXW23LFNZ2KUY3PNVWWK3TUL5UWJTSUXVVEE.gif> Message ID: ***@***.***>

[ClimberMel]

The data seems to be in reverse order from what it used to be. I'm not sure if that is really an issue since there is a resort parameter that will switch the order of the returned data.

[ClimberMel]

The remaining issue was reported by Lew. He had an array of 20000 rows and it brought back from inception date, but stopped at 2020-12-31. That seemed odd.
The formula is = RCHGetYahooHistory("TQQQ", , , , , , , , "DA", 1, 1, 1, 20000, 2) but I switched it to = RCHGetYahooHistory("TQQQ", , , , , , , , "DA", 1, 1, 0, 20000, 2) to check the resort and yes switching the resort from 1 to 0 reversed the order, but still stops at 2020-12-31. That is 2743 rows, so it isn't a limit issue as that should be an even 1000 or 10000 depending.

[ClimberMel]

I'll close this issue as fixed. Most of these are fixed in the 3.0.2023.02.09 version and the sorting will be addressed in the 3.0.2023.02.15 version soon to be released.