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

Updated Elements for smf-elements-1.txt Enclosed #46

Closed Bruce-95 closed 1 year ago

Bruce-95 commented 1 year ago

Hi Mel,

here are some updated element numbers that were not yet defined in the smf-elements-1.txt file.

57;YahooPrice;50-DMA Chg;=smfGetYahooJSONData("~","price","regularMarketPrice",,) - smfGetYahooJSONData("~","summaryDetail","fiftyDayAverage",,) 58;YahooPrice;50-DMA Chg %;=(smfGetYahooJSONData("~","price","regularMarketPrice",,) - smfGetYahooJSONData("~","summaryDetail","fiftyDayAverage",,)) / smfGetYahooJSONData("~","summaryDetail","fiftyDayAverage",,) 60;YahooPrice;200-DMA Chg;=smfGetYahooJSONData("~","price","regularMarketPrice",,) - smfGetYahooJSONData("~","summaryDetail","twoHundredDayAverage",,) 61;YahooPrice;200-DMA Chg %;=(smfGetYahooJSONData("~","price","regularMarketPrice",,) - smfGetYahooJSONData("~","summaryDetail","twoHundredDayAverage",,)) / smfGetYahooJSONData("~","summaryDetail","twoHundredDayAverage",,) 64;YahooPrice;52-Wk Low Chg;=smfGetYahooJSONData("~","price","regularMarketPrice",,) - smfGetYahooJSONData("~","summaryDetail","fiftyTwoWeekLow",,) 65;YahooPrice;52-Wk Low Chg %;=(smfGetYahooJSONData("~","price","regularMarketPrice",,) - smfGetYahooJSONData("~","summaryDetail","fiftyTwoWeekLow",,)) / smfGetYahooJSONData("~","summaryDetail","fiftyTwoWeekLow",,) 67;YahooPrice;52-Wk High Chg;=smfGetYahooJSONData("~","price","regularMarketPrice",,) - smfGetYahooJSONData("~","summaryDetail","fiftyTwoWeekHigh",,) 68;YahooPrice;52-Wk High Chg %;=(smfGetYahooJSONData("~","price","regularMarketPrice",,) - smfGetYahooJSONData("~","summaryDetail","fiftyTwoWeekHigh",,)) / smfGetYahooJSONData("~","summaryDetail","fiftyTwoWeekHigh",,)

90;YahooCE;Earnings Date Start;=smfGetYahooJSONData("~","calendarEvents","earningsDate",,"fmt") 91;YahooCE;Earnings Date End;=smfGetYahooJSONData("~","calendarEvents","earningsDate",,"fmt") -or - 90;YahooCE;Earnings Date Start;=smfUnix2DateStr(smfGetYahooJSONData("~","calendarEvents","earningsDate",,), "mm/dd/yyyy") 91;YahooCE;Earnings Date End;=smfUnix2DateStr(smfGetYahooJSONData("~","calendarEvents","earningsDate",,), "mm/dd/yyyy")

Notes: -- The calculated values for elements 57-68 above match the same YPV values up to 3 to 7 decimal places. Had to calculate them because there were no relevant field names in any of the modules. Also tried to shorten the calculations by using the RCHGetElement() function instead but it returned errors. For example, these don't work:

57;YahooPrice;50-DMA Chg;=RCHGetElementNumber("~",15) - RCHGetElementNumber("~",56) 58;YahooPrice;50-DMA Chg %;=RCHGetElementNumber("~",57) / RCHGetElementNumber("~",56)

Guess it doesn't like recursion.

-- Re element 91: There's no "field name" in the JSON file for "Earnings Date End". The "earningsDate" field could have one date (start-date) or two dates (start-date and end-date). It would take some convoluted coding to determine how many dates there were and grab the right one. Since there's no unique field name for 91 I'm for using the same one as 90. I don't think it will bother anyone. Besides I'm a minimalist..... :-)

Examples of one and two dates:

https://query2.finance.yahoo.com/v11/finance/quoteSummary/IBM?modules=calendarEvents https://query2.finance.yahoo.com/v11/finance/quoteSummary/AAPL?modules=calendarEvents

-- I could not find anything in any modules for these:

6;YahooPrice;Market;="Not available" 8;YahooPrice;Source Interval;="Not defined yet" 12;YahooPrice;Language;="Not defined yet" 77;YahooSD;Tradeable;="No data"

Again feel free to change.

Regards, ~ Bruce S

ClimberMel commented 1 year ago

I'm adding in those changes to Elements-1.txt