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!

Python for SMF?

Could I utilize Python for SMF?

I have done some work with calling python modules from within Excel.  My issue has been that I have been unable to write to the currently open workbook.

For myself, I use python to get market data and data from IB (Interactive Brokers) and I write some of that data to Excel files.  It is quite easy to for python to read, create and modify data in Excel files.

There are add-ins to do this like PyXLL or XLWings, but they are somewhat commercial products.  I say somewhat since PyXLL has a 30 day demo and XLWings has an open source version that is free if somewhat feature limited.  In the spirit of Randy's SMF Add-In, I am working on a open source and free way of implementing python solutions for Stock Market Functions.

Some ideas that I am testing are creating some python utilities to do some of the work of the SMF Add-In that could work alongside of it.  Use it to get slow or hard to get data, write it to CSV or XLSX files that can be linked to a Stock Dashboard or other Workbook that can utilize that data.

New Release in Progress

 I have added these issues to the next release:

    - smfGetYahooPortfolioView possible incorrect data returned #9

    -  smfGetYahooHistory close / adjclose using wrong pItem codes #19

    -  Duplicate Excel 2010 Function #18

 

There was no issue with smfGetYahooPortfolioView.  The user was also calling smfGetYahooHistory and that is where they were getting unadjusted intead of adjusted close

Issue# 18 is some unused code in the add-in that matched an Excel internal function and will be deleted.  It may have been originally written before Excel added the IFERROR() function.

The following modules will be updated:

RCHGetYahooHistory() & smfGetYahooHistory to change the Items back to C for adjusted close and U for unadjusted close.  A will now be allowed as well, but simply be converted to use C for adjusted.

smfUtilities_ to remove function IfError().

modGetElementNumber to update version number