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

Element 11 #37

Closed ClimberMel closed 1 year ago

ClimberMel commented 1 year ago

I am adding all the smfGetYahooPortfolioView Items into smf-elements-1.txt to use with RCHGetElementNumber

Here are elements 11 & 16 for examples 11;YahooQT;GMT Offset Milliseconds;=smfGetYahooJSONData("~","quoteType","gmtOffSetMilliseconds",,"") 16;YahooPrice;Last Traded (UNIX);=smfGetYahooJSONData("~","price","regularMarketTime",,"")

The JSON comes from

Here is the JSON: image

Instead of getting the value for key gmtOffSetMilliseconds, it is getting the next item in quotes which is maxAge

I also tried using JsonConverter, but it fails on that as well...

Bruce-95 commented 1 year ago

Mel,

I tested some code changes to my copy of the smfGetYahooJSONData() function in module modGetYahooJSONData and it appears to fix Issue #37. In the area where the "Type" parameter is evaluated in smfGetYahooJSONData(), I've add a CASE stmt.

... Select Case LCase(pProcess) Case "": s1 = smfStrExtr(s1, """", """") Case "fmt": s1 = smfStrExtr(s1, """fmt"":""", """") Case "raw": s1 = smfStrExtr(s1, """raw"":", ",") Case "num": s1 = smfStrExtr(s1, "~", ",""") ' <== fix for Issue #37 Case Else: s1 = smfStrExtr(s1, """raw"":", ",") End Select ...

This requires a new Type parameter of "num" when calling the function for certain Field_Names. This is what I got for APPL on 06/03/23 11:35am after the change

=smfGetYahooJSONData("AAPL", "quoteType", "gmtOffSetMilliseconds", , "num") ........ returns -14400000 =smfGetYahooJSONData("AAPL", "quoteType","firstTradeDateEpochUtc",,"num") .......... returns 345479400 =smfGetYahooJSONData("AAPL","price","regularMarketTime",,"num") ................................ returns 1685736005

These match the data in the "APPL" JSON file(s). I have not done an exhaustive test for other date/time fields. It seems you'll need the "num" Type when extracting any date/time fields or numeric fields that are not prefixed with "raw", "fmt" or are not quoted.

As for "smf-elements-1.txt" , you would add "num" to the following: ... 11;YahooQT;GMT Offset Milliseconds;=smfGetYahooJSONData("~","quoteType","gmtOffSetMilliseconds",,"num")

16;YahooPrice;Last Traded (UNIX);=smfGetYahooJSONData("~","price","regularMarketTime",,"num") ...

Running =RCHGetElementNumber("AAPL",11) and =RCHGetElementNumber("AAPL",16) work as well with the new entries.

As I have never used Github (even this is my 1st time making a comment) I will leave it to you to make the changes after you have vetted the code.

To summarize,

Capture2

Capture

Will try to answer any questions, ~ Bruce

ClimberMel commented 1 year ago

Thanks Bruce! I'm making tomorrow an office day, so I'll check out bot your code and the html. I tend to just write html, plus I have css to take care of most of the formatting and keep the pages consistent. But I'm sure it will save me a lot of time.

Mel

ClimberMel commented 1 year ago

I'm adding those changes to the functions and hope to have a new version out today or tomorrow. I am including updates to the elements files and I will update the documentation soon.