Friday, November 17, 2017

Alert -- All reuters.com element definitions updated

Reuters.com made several changes to their URLs, including changing to "https://" instead of "http://", so all of their defined elements on smf-elements-5.txt needed to be updated.

New files on the web site: 

Best practice is to exit EXCEL when updating SMF add-in files.

Tuesday, November 14, 2017

Alert -- All stockscores.com element definitions updated

Stockscores.com just changed their URLs to be "https://" instead of "http://", so all of their defined elements on smf-elements-9.txt needed to be updated.

New files on the web site: 
Best practice is to exit EXCEL when updating SMF add-in files.

Sunday, November 12, 2017

New ZIP File -- Version 2017.11.11

I just uploaded a new version of the ZIP archive. 

New files on the web site: 

Best practice is to exit EXCEL when updating SMF add-in files.



Changes since 2017.11.08: 

  • smfGetYahooPortfolioView() -- Backed out percentage adjustments for fields 58, 61, 65, 68
  • smfGetYahooPortfolioView() -- Fixed field list adjustment when only default fields are requested



The second change should fix errors for most of you that were having trouble getting a single data field with something like:


=smfGetYahooPortfolioView("MMM","15")


Wednesday, November 8, 2017

New ZIP File -- Version 2017.11.08

I just uploaded a new version of the ZIP archive. 

New files on the web site: 

Best practice is to exit EXCEL when updating SMF add-in files.



Changes since 2017.11.04: 
  • smfGetYahooPortfolioView() -- Fix errors on percentage adjustments when value returned is non-numeric 
  • smfGetYahooPortfolioView() -- Adjust GMT date/times by GMT offset
  • smfGetYahooPortfolioView() -- Allow first ticker of "None" to turn off retrieval 
  • smfGetBarchartPortfolioView() -- New function to get multiple stock quotes/data from Barchart with single Internet access
  • smfMenuRecalculationSelection  -- Fix context menu processing of recalculating a selected range



The new smfGetBarchartPortfolioView() is basically just the smfGetYahooPortfolioView() function pointing at a Barchart JSON file instead of Yahoo JSON file. But the Barchart file has 246 fields available instead of Yahoo's 91 fields, including a number of technical indicator values.

The change to the smfMenuRecalculationSelection macro fixes the context menu option to calculate a selected range of the worksheet. It can do a selective version of the smfForceRecalculation macro. However, it ONLY updates that range. It will not update any part of the worksheet outside of that range, even references to items in that range that changed.

Saturday, November 4, 2017

New ZIP File -- Version 2017.11.04

I just uploaded a new ZIP file. It's on the front page of the web site as a "beta" version, so that the 2017.11.02 version is still available for download. There were a number of changes, and they appear to be working OK, but I wanted people to have an option to go back if necessary.

New files on the web site: 
There were a number of changes to the smfGetYahooPortfolioView() function:

DateRoutineDescription
2017-11-04smfGetYahooPortfolioView• Add over 50 additional fields
• Create list of requested fields instead of asking for everything
• Maintain order of ticker symbol requests
• Add creation of EXCEL serial date/time values
• Divide percentage fields by 100, as needed
• Fix earnings dates
2017-11-04RCHGetElementNumberCorrected definition of Yahoo element number 13863 (company
 name) on smf-elements-2.txt
2017-11-03smfGetYahooPortfolioViewFix to handle non-US currency combinations

I changed the smfGetYahooPortfolioView() example so it has a more "intelligent" default list of fields instead of just a "data dump". I also added a tab with a list of all of the available fields.

One issue I haven't decided if I'll leave as-is or handle -- if you pass the same ticker symbol twice, data will only be filled in on the row where it first appears.

Again, I'd like to stress that people NOT use this function to retrieve a lot of individual data items with one function per worksheet cell. It should be array-entered over a range to grab all the desired data with one Internet access. Otherwise, Yahoo may be inundated with a large number of data requests. I wouldn't want them to take steps in removing access to the data. 

The easiest way to do this is to have one sheet that retrieves all the data and then use other sheets to look up data out of that sheet and present it as desired. It will also make transition easier if you need to change things, because all of the retrieved data will be in one location.

Friday, November 3, 2017

Tip -- Possible Google method to get current quotes

I recently was referred to this web page on "Free Stock Quotes in Excel". Not up-to-date, but the Google method was interesting, so I tried it. For some of you, it might be a good alternative or back-up. The primary drawback is that you would manually need to maintain your ticker list within the Google sheet.

See this web page for data items available from the Google Finance API.

I used the method to publish a CSV file of quotes data for the S&P 500 stocks. To get it into EXCEL, all that is needed is:

=smfGetCSVFile("https://docs.google.com/spreadsheets/d/e/2PACX-1vRVFIQcRFRobB_tnWo-tiVZ9J8s74RExHlQulpumJLWsF1RbmhrfcPs1vZnvAc9-OEqk0tcZKSIGHR9/pub?output=csv")

Seemed fast to me, for data on 500 stocks. The first few lines look like:


TickerTradetimePriceChangeChange %OpenHighLowVolumePrev Close52-Wk Low52-Wk HighPEEPS
A11/3/2017 15:44:12$68.28$0.150.22$68.09$68.67$68.01765380$68.13$42.92$68.6735.21.94
AAL11/3/2017 15:44:34$47.47$0.110.23$47.36$47.61$46.802837542$47.36$39.21$54.4812.083.93
AAP11/3/2017 15:44:28$81.96$0.270.33$81.39$82.38$80.27879400$81.69$80.21$177.8316.375.01
AAPL11/3/2017 15:46:35$172.61$4.502.68$174.00$174.26$171.1252786306$168.11$104.08$174.2619.638.79
 

Thursday, November 2, 2017

Alert -- Official notice by Yahoo

According to this and this:

"It has come to our attention that this service is being used in violation of the Yahoo Terms of Service.  As such, the service is being discontinued.  For all future markets and equities data research, please refer to finance.yahoo.com."

So it appears RCHGetYahooQuotes() may be no more.