search

ClimberMel / SMF_Add-in

Stock Market Functions for Excel using VBA
https://climbermel.github.io/SMF_Add-in/
14 stars 5 forks source link

RCHGetYahooHistory errors #2

Closed ClimberMel closed 1 year ago

ClimberMel commented 1 year ago

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 commented 1 year ago

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

aggie81 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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.

screenshot1 screenshot2

ClimberMel commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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

ClimberMel commented 1 year ago

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 https://github.com/ClimberMel/SMF_Add-in/issues/2#issuecomment-1421699650 , 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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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.