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

smfGetYahooOptionQuote() Needs "crumb" Value to Retrieve Web Page Data #67

Closed Bruce-95 closed 2 months ago

Bruce-95 commented 3 months ago

Mel,

People are complaining that =smfGetOptionQuotes() is not working anymore. Yahoo's recent changes affected the =smfGetYahooOptionQuote() function which is called by =smfGetOptionQuotes(). Basically the function now needs to get the web page using a "crumb". Plus I removed 3 items that are no longer available on the web page - see comments in =smfGetYahooOptionQuote(). (You'll need to change the documentation for =smfGetOptionQuotes() and =smfGetYahooOptionQuote() re the 3 items ). I used this web page for testing.

Attached is the modGetOptionQuotes module with the changes.

~ Bruce S

modGetOptionQuotes.zip

Bruce-95 commented 3 months ago

Mel,

hold off installing this. I'm working on a version that shouldn't be affected by future web page changes.

~ Bruce

ClimberMel commented 3 months ago

OK I'll wait u tilI hear back from you.Thanks.MelSent from my Galaxy -------- Original message --------From: Bruce S @.> Date: 2024-04-26 4:18 p.m. (GMT-08:00) To: ClimberMel/SMF_Add-in @.> Cc: Subscribed @.***> Subject: Re: [ClimberMel/SMF_Add-in] smfGetYahooOptionQuote() Needs "crumb" Value to Retrieve Web Page Data (Issue #67) Mel, hold off installing this. I'm working on a version that shouldn't be affected by future web page changes. ~ Bruce

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you are subscribed to this thread.Message ID: @.***>

Bruce-95 commented 3 months ago

Updated solution

Mel,

People are saying that =smfGetOptionQuotes() is not working anymore. Yahoo's recent changes affected the =smfGetYahooOptionQuote() function which is called by =smfGetOptionQuotes().

In =smfGetYahooOptionQuote(), I replaced the smfGetTagContent() functions with smfGetYahooJSONData(). This gets the data from the source JSON instead of scraping Yahoo's web page. This should avoid any future problems with Yahoo changing their web page(s).

The replaced/old smfGetYahooOptionQuote is now called smfGetYahooOptionQuote1.

Attached is the updated modGetOptionQuotes module.

~ Bruce S modGetOptionQuotes.zip

ClimberMel commented 3 months ago

I'll put this together this weekend.  Did you happen to look at historical data for options?  I was just looking at that today for a different project... didn't know Yahoo had that.MelSent from my Galaxy -------- Original message --------From: Bruce S @.> Date: 2024-04-27 1:37 p.m. (GMT-08:00) To: ClimberMel/SMF_Add-in @.> Cc: ClimberMel @.>, Comment @.> Subject: Re: [ClimberMel/SMF_Add-in] smfGetYahooOptionQuote() Needs "crumb" Value to Retrieve Web Page Data (Issue #67) Updated solution Mel, People are saying that =smfGetOptionQuotes() is not working anymore. Yahoo's recent changes affected the =smfGetYahooOptionQuote() function which is called by =smfGetOptionQuotes(). In =smfGetYahooOptionQuote(), I replaced the smfGetTagContent() functions with smfGetYahooJSONData(). This gets the data from the source JSON instead of scraping Yahoo's web page. This should avoid any future problems with Yahoo changing their web page(s). The replaced/old smfGetYahooOptionQuote is now called smfGetYahooOptionQuote1. Attached is the updated modGetOptionQuotes module. ~ Bruce S modGetOptionQuotes.zip

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>

Bruce-95 commented 3 months ago

I wasn't aware Yahoo had option data history. I googled "yahoo option data history" and found this python-related.

https://stackoverflow.com/questions/77145106/how-to-get-historical-price-of-options-with-strike-price-y-relative-to-being-set

ClimberMel commented 3 months ago

It turns out all you need is to use smfGetYahooHistory and use an option ticker instead of a stock.  Like SPXW240426P05305000 works.Sent from my Galaxy -------- Original message --------From: Bruce S @.> Date: 2024-04-27 2:32 p.m. (GMT-08:00) To: ClimberMel/SMF_Add-in @.> Cc: ClimberMel @.>, Comment @.> Subject: Re: [ClimberMel/SMF_Add-in] smfGetYahooOptionQuote() Needs "crumb" Value to Retrieve Web Page Data (Issue #67) I wasn't aware Yahoo had option data history. I googled "yahoo option data history" and found this python-related. https://stackoverflow.com/questions/77145106/how-to-get-historical-price-of-options-with-strike-price-y-relative-to-being-set

—Reply to this email directly, view it on GitHub, or unsubscribe.You are receiving this because you commented.Message ID: @.***>

Bruce-95 commented 2 months ago

Huh, learned something new. I wonder how many others know that. You should put that in smfGetYahooHistory 's documentation.