Alert -- All etfscreen.com element definitions updated

ETFScreen.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: 

• Change-Log.html
• RCHGetElementNumber-Element-Definitions.xls
• smf-elements-9.txt

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

New ZIP File -- Version 2017.11.30

I just uploaded a new version of the ZIP archive.

New files on the web site: 

• Change-Log.html
• RCH_Stock_Market_Functions-2.1.2017.11.30.zip
• smfGetBarchartPortfolioView-Function.html
• smfGetYahooPortfolioView-Function.html

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

---

Changes since the 2017.11.11 ZIP archive: 

• smfGetBarchartPortfolioView() -- Added parameter to allow data for multiple occurrences of a ticker
• smfGetYahooPortfolioView() -- Added parameter to allow data for multiple occurrences of a ticker
• smfGetGoogleHistoryCSV() -- Changed URL of CSV file
• Previously announced element definition changes for Stockscores, Reuters, Yahoo, and Morningstar

---

Alert -- All yahoo.com mutual fund element definitions updated

I updated all of the yahoo.com mutual fund element definitions on smf-elements-2.txt to extract data from Yahoo's new JSON files.

New files on the web site: 

• Change-Log.html
• RCHGetElementNumber-Element-Definitions.xls
• smf-elements-2.txt

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

Some elements are still obsolete, because they don't exist on the JSON files (at least not that I saw). I also added a number of elements that I found on the JSON file.

I don't use mutual funds, so if you notice data on their web site that I haven't defined, let me know and I'll check to see if I can find the data on a JSON file or if it can be pulled from a web page. Note that some of these elements do work for ETFs, but not all.

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: 

• Change-Log.html
• RCHGetElementNumber-Element-Definitions.xls
• smf-elements-5.txt

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

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: 

• Change-Log.html
• RCHGetElementNumber-Element-Definitions.xls
• smf-elements-9.txt

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

New ZIP File -- Version 2017.11.11

I just uploaded a new version of the ZIP archive. 

New files on the web site: 

• Change-Log.html
• RCH_Stock_Market_Functions-2.1.2017.11.11.zip

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")

New ZIP File -- Version 2017.11.08

I just uploaded a new version of the ZIP archive. 

New files on the web site: 

• Change-Log.html
• RCH_Stock_Market_Functions-2.1.2017.11.08.zip
• smfGetBarchartPortfolioView-Example.xls
• smfGetBarchartPortfolioView-Function.html
• smfGetYahooPortfolioView-Function.html

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.

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: 

• Change-Log.html
• RCH_Stock_Market_Functions-2.1.2017.11.04.zip
• smfGetYahooPortfolioView-Example.xls
• smf-elements-2.txt

There were a number of changes to the smfGetYahooPortfolioView() function:

Date	Routine	Description
2017-11-04	smfGetYahooPortfolioView	• 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-04	RCHGetElementNumber	Corrected definition of Yahoo element number 13863 (company  name) on smf-elements-2.txt
2017-11-03	smfGetYahooPortfolioView	Fix 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.

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:

Ticker	Tradetime	Price	Change	Change %	Open	High	Low	Volume	Prev Close	52-Wk Low	52-Wk High	PE	EPS
A	11/3/2017 15:44:12	$68.28	$0.15	0.22	$68.09	$68.67	$68.01	765380	$68.13	$42.92	$68.67	35.2	1.94
AAL	11/3/2017 15:44:34	$47.47	$0.11	0.23	$47.36	$47.61	$46.80	2837542	$47.36	$39.21	$54.48	12.08	3.93
AAP	11/3/2017 15:44:28	$81.96	$0.27	0.33	$81.39	$82.38	$80.27	879400	$81.69	$80.21	$177.83	16.37	5.01
AAPL	11/3/2017 15:46:35	$172.61	$4.50	2.68	$174.00	$174.26	$171.12	52786306	$168.11	$104.08	$174.26	19.63	8.79

 

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.

Alert -- Yahoo historical quotes

I was just informed that someone was having problems getting historical quotes from Yahoo for November 1st. It looks like the data isn't on Yahoo.

So they may have had more issues yesterday than just the current quotes process?

New ZIP File -- Version 2017.11.02

First off, my apologies. I rushed the release of the new smfGetYahooPortfolioView() routine, trying to give people a workaround for the Yahoo current quotes problem.

New files on the web site: 

• Change-Log.html
• RCH_Stock_Market_Functions-2.1.2017.11.02.zip

After reviewing problems people were having, I had to make two minor changes to the routine:

1. Upped the maximum expected length of a company's data from 1200 bytes to 2000 bytes.
2. Account for commas in the company name. I was basically dividing up data by commas.

I'm no longer seeing the glitches that were reported, but I can't guarantee some don't still exist.

Again, my apologies.

New ZIP File -- Version 2017.10.31

Because of the Yahoo issues preventing RCHGetYahooQuotes() from working, I just updated the ZIP archive. It contains an update for the smfGetYahooPortfolioView() function, which can be a workaround until Yahoo fixes whatever problem they are having with current quotes.

New files on the web site: 

• Change-Log.html
• RCH_Stock_Market_Functions-2.1.2017.10.31.zip
• smfGetYahooPortfolioView-Example.xls
• smfGetGuruFocusCSVItem-Examples.xls
• smf-elements-9.txt

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

Changes since 2017.09.17:

• I rewrote smfGetYahooPortfolioView() so that it will run much faster than before, and allow a lot more quotes to be retrieved. It also retrieves option quotes (using Yahoo option symbols). For now, the only documentation is the above example workbook. Parameters are symbol list, field list ("01" through "34"), and whether to put headers on the output. You should array-enter it to have as few function invocations as possible. For example:
  
  =smfGetYahooPortfolioView("MMM,IBM","010215",,1)
  
  
• I wrote a new smfGetGuruFocusCSVItem() function, which will be useful to GuruFocus subscribers. It extracts data items out of the CSV file they provide, which contains up to 30 years and 120 quarters of financial statements data and ratios. Currently, the only documentation is the above example workbook. Parameters are ticker, line item label, and time period (1 to 153 or "A29" to "A0" and "Q119" to "Q0"). For example:
  
  =smfGetGuruFocusCSVItem("AGX","Net-Net Working Capital","A0")
  
  
• Updated all IBD elements on smf-element-9.txt to use "https://" instead of "http://".


• Rewrote the Yahoo extraction in smfGetOptionStrikes(). It used to extract the top-level list of option strikes. It now extracts the option strikes specific to a given expiration date. In addition, the expiration date now defaults to a value of 0, which allows data from the first available expiration date to be used.


• Obsoleted all of the OptionsXpress function invocations, by having them return messages indicating OptionsXpress is no longer usable.


• Both smfStrExtr() and smfWord() now have a "pConvert" parameter, to attempt a numeric conversion of the extracted string. This will allow removal of the smfConvertData() function around them, so the functions no longer need to be nested.

Alert -- RCHGetYahooQuotes() problems.

I'm currently getting "Error" returned for all of my RCHGetYahooQuotes() invocations. The message on the Yahoo website is:

• Sorry, Unable to process request at this time -- error 999

Hopefully, this is just a temporary issue and not a permanent change.

Update -- All IBD element definitions

All IBD element definitions on smf-elements-9.txt have been updated to use "https://" instead of "http://" in their URLs.

New files on the website:

• Change Log
• smf-elements-9.txt

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

 

optionsXpress no longer a usable data source

Since optionsXpress has integrated with Schwab, the web pages being used by various SMF add-in functions no longer exist. I don't foresee any updates to fix those extractions.

Affected functions:

• smfGetOptionQuotes()
• smfGetOXOptionQuote()
• smfGetOX2OptionQuote()
• smfGetOX3OptionQuote()
• smfGetOptionStrikes()
• smfGetOptionExpirations()

Warning -- Google updates coming

For those of you that grab data from Google, Google just issued a notice to users that they’ll be overhauling the Google Finance platform in November, offering a completely new system of following, tracking, and getting news and information about stocks.

Upcoming Google Changes

So be aware of potential issues. The recent URL change and the recent problems with their historical quotes CSV files may have just been a first step.

New ZIP file -- Version 2017.09.17

I'm starting this blog as an experiment, to use it as a way to note issues that have come up or updates that have been made to files on the web site. That way, people can keep up-to-date without having to deal with all the background noise on the Yahoo group.

For today, it was noted that the smfGetGuruFocusItem() function was not working. It turned out to be a simple label change on their web page, but did require an update to the XLA file.

I'm also taking this opportunity to phase out the beta release of the add-in and made the new ZIP file the official release. New files on the web site:

• Change-Log.html
• RCH_Stock_Market_Functions-2.1.2017.09.17.zip

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




You should subscribe to this RSS feed to get any updates. If you don't use an RSS reader, BlogTrottr can be used to send RSS feed updates as emails to your email client.