SMF Add-In

Sunday, June 18, 2023

Notice that Zack's had a data breach

Just in case you use Zack's web site:

This notice is regarding a prior data breach disclosed by Zacks at https://www.zacks.com/breach/. We have confirmed that in association with this breach relating to a smaller subset of customers whose unencrypted passwords were compromised, the unauthorized third parties also gained access to encrypted passwords of zacks.com customers, but only in the encrypted format.

Wednesday, June 14, 2023

New and updated documentation

I have updated a number of the pages on the web site.

I added a page to array enter a formula

How to fix links when Excel hard codes the location

Added documentation for smfStrExtr function and smfWord function

Why does RCHGetElementNumber return Undefined or Error

I cleaned off all the broken links from Tips & FAQs page. I will add to it as I get more documentation.

Thursday, June 8, 2023

Some detail on the YPV fix

Some details on the fix for the smfGetYahooPortfolioView (YPV since I don't like typing that much!)

The Yahoo data was still there, but it was blocked unless you were on their site. If you went to https://finance.yahoo.com/ and then went to MyPortfolio, you could select the data for any stocks you added to your portfolio. The API that people have been using the last number of years for Yahoo is not a supported public API. It is in place for Yahoo's web pages to fetch the data to display on their web pages. However, some people have been using these unsupported APIs to get market data... and the SMF Add-In is one such example. It will retrieve close to 90 data elements in relation to the data provided on the Portfolio View page.

When Yahoo changed the API to require authentication, it was very disappointing (I did hear some stronger words used) to many people. I did get almost all of those data elements working with the RCHGetElementNumber funtion, but the problem with that function was it made so many calls to Yahoo's pages to get the elements for each ticker. That was the main reason to move people to use the YPV function... it could be array entered into Excel and then it would retrieve all the requested elements for all the requested tickers in ONE CALL to Yahoo and return all that data in JSON format. Then the VBA module could parse all the data out and insert it into the Excel sheet.

One of the members, Bruce, found some info on retrieving the crumb info from yahoo and while I was working on that, Tony (another member of the group) put together the fix while I went to get some sleep... procrastination wins again!

So now smfGetYahooPortfolioView has an additional parameter internally for calling RCHGetWebData which really just calls smfGetWebPage. That calls RCHGetURLData when then in the case of the new fix, calls a new function RCHGetURLData4. That new function calls another new function called getYahooCookieCrumb that gets and stores the cookie and crumb data for the resulting calls. This allows the add-in to fetch the data by making the web page think it has been authorized to access it as an online user would be.

NOTE:

Even though I added over 100 new elements to the RCHGetElementNumber function, only use it for when you need a very few items. For spreadsheets of lots of data, use the YPV function as an ARRAY ENTERED FORMULA.

If you want reliable real time data for many many tickers and plan to keep it up to date all day long, please read Yahoo's rules on data usage and look at getting a streaming data feed. If people abuse the data from Yahoo, they will continue to make changes with absolutely no notice to curtail the unwelcome traffic!

SMF new Add-In version 3.0.2023.06.08

The function smfGetYahooPortfolioView is now fixed!

Thanks to help from the group, they provided information and code changes to utilize cookie and crumb into the web calls so that portfolio view will now once again return the JSON data.

The code updates are on my GitHub and the new version is posted on the SMF website.

Thursday, May 25, 2023

Now smfGetYahooPortfolioView has broken

I put in fixes for one function and Yahoo breaks another!

So smfGetYahooPortfolioView started out as being flaky... It would work then it would give an error, then work...

Today I can't get anything using the API to work. The json response for YahooPortfolio gives a 404 not found or unauthorized access depending on how you try to access it.

I found this developer feedbak form:

Yahoo Finance | API Feedback

We’re sorry for the inconvenience, but API-level access to Yahoo Finance quotes data has been disabled.

Yahoo Finance licenses data from 3rd-party providers that do not currently authorize us to redistribute these data in API form. Licenses that authorize redistribution come with a greater cost that varies depending on a number of factors, including whether the data is for personal or commercial use, the type of data, the volume of queries, and additional features which may be available.

We would appreciate your feedback to ensure that we can continue to serve your needs. By understanding your intended use of these API data, we will be better able to acquire the appropriate licenses. We appreciate your feedback, and we read every response.

What types of data are you interested in accessing via an API?

Please select all that apply.

Real-time stock quotes data

Historical stock quotes data

Bonds data

Currency data

Cryptocurrency data

Options data

ETF data

Futures data

Indices data

Mutual funds data

Company earnings data

Company financial statements

Company valuation data

Other:

What kind of applications have you developed that used API data?

Please select all that apply.

Charting application

Investing model

Performance tracking

Portfolio analysis

Trading application

Other:

Is the application you developed for personal or commercial use?

For personal use (just me)

For commercial use (more than me)

Both personal and commercial use

Would you be willing to pay for API data access?

Yes

If yes, what price per month (US dollars) would you be willing to pay?

Again, we are sorry for the inconvenience, and we thank you for your feedback. If you would like to help us craft this new API service, please provide your email address so that we may contact you.

Tuesday, May 23, 2023

ZACKS Elements fixed

I have fixed a number of Zacks data elements. I was able to fix 852 that was marked Obsolete and I also added some new elements for the Earnings ESP.

The ESP (Expected Surprise Prediction) only had the current ESP 13894. I added 13897 - 13899 for the ESP previous Quarter, 2 Quarter previous and 3 Quarters previous

Here are the fixes added to smf-elements-20.txt for now:

Saturday, May 20, 2023

SMF new Add-In version 3.0.2023.05.20

I have posted an updated version 3.0.2023.05.20 to the website

The new version fixes these errors. New processing added to modGetTagContent. smfGetTagContent to handle element 848 and smf-elements-6.txt updated to fix element 13892.

The issues are detailed on GitHub

Issue #31 RCHGetElement #13892 is wrong

Issue #32 RCHGetElement #848 is wrong