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