Ultimate Excel Stock Quotes Downloader

The Ultimate Excel Stock Quotes Downloader (USD) is the most versatile downloader available and it's free! Download daily, historical, and dividend prices using the Tiingo.com API (also free).

USD is updated periodically with new features. Check back occasionally for the latest version. The version number is at the top of the Instructions tab.

Current version: 2.10

See the bottom of the page for instructions on how to update your file.


Note: Tiingo, which provides the API and data for USD, has ended support for TLS 1.0 and 1.1 (security protocols), which Windows 7 uses to connect to the API. If you are running Windows 7, USD may not work unless you make some registry changes and (possibly) install an optional update. The issue is described here, and the update is here. If you try to install the update, it may say that it's not applicable to your machine, but you may still need to make the registry changes described farther down the page, under the subheads "Easy fix" and "Enable TLS 1.1 and 1.2 on Windows 7 at the SChannel component level."

Get Ultimate Excel Stock Quotes Downloader now, for Excel 2007 and later. This software is free to download and use.* Also requires free registration at Tiingo.com to use their API.

If you like this software, please consider making a contribution to the developer.

Key Features include:

  • Download last, open, high, low, and stock names.
  • Use extra columns for custom calculations.

  • Download daily, weekly, or monthly prices.
  • Select actual or adjusted prices.
  • Download a range of days or a single day.
  • Save any column by placing a "y" in the Keep row (5).

  • Use a global date range for all dividends or a custom range for each row.
  • Download dividends through today by placing a "t" in the To column (J).
  • Download dividends from n months previous by placing "t-n" in the From column (I).
  • Save any row by placing a "y" in the Keep column (M).
  • Use columns E-H for custom calculations or hide if not needed.

javascript button

Get Ultimate Excel Stock Quotes Downloader now, for Excel 2007 and later. This software is free to download and use.* Also requires free registration at Tiingo.com to use their API.

If you like this software, please consider making a contribution to the developer.

   Note

API

This downloader uses the Tiingo API, which requires free registration at tiingo.com. (They also have a paid plan with higher data limits, but the free plan should be adequate for average users.) 

Rows / columns

The download macros are designed to work with the current row and column setup. If you add or delete rows or columns, the data will be in the wrong cells or may not appear at all unless you modify the code.

Symbols

On all sheets, the symbols must be in column D. You can use columns A-C for anything else for example, a mutual fund sponsor's name, stock or fund sector, or other information.

Note: The symbols shown in the spreadsheet are examples only and do not constitute recommendations.

Auto- delete

If you delete symbols from the list, the macro will delete those rows the next time the prices are updated.

Screen refresh

A download may appear to hang or may show "Not Responding" in the title bar, but it is actually computing faster than the screen can update. The screen will refresh when the macro ends.


   Authentication Token

When you register at Tiingo.com, you will receive a username, password, and authentication token. The token allows you to download prices regardless of whether you are logged in to the site. You can find your token on several pages of the site when you are logged in:

Paste the authentication token into cell D3 on the Daily tab.

You can view your usage and limits at https://api.tiingo.com/account/usage.


   Daily Tab

Click the Daily button to update. The Tiingo authentication token must be present in cell D3 for the application to operate.

Names

To obtain the names of the symbols, click the Names button. This is a separate set of API calls and should not need to be done more than once, unless you make significant changes to the symbol list.


   Historical Tab

  1. Enter From and To dates in cells D3 and D4. To download a single date, you can enter just the To date.

  2. Select Daily, Weekly, or Monthly prices from the dropdown list in D1 and Actual or Adjusted prices from the dropdown in D2.

  3. Click the Historical button. The prices appear in column F and additional columns to the right. If you enter a new date range smaller than the previous one, the macro deletes extra columns at the right, and if you enter a new date range larger than the previous one, it overwrites anything to the right of the existing last column.

No data

When downloading a single date, if no data appear, there was no trading on the To date.

y

To save any columns, enter "y" (without quotes) in the Keep row (5) above them. These columns will move to the left (column F) and will be retained as long as the "y" remains in row 5. The background color for the date (row 6) of each saved column changes from yellow to orange.


   Dividends Tab

Enter From and To dates in cells I5 and J5. Click the Divs button.

By symbol

To download a different date range for each symbol, enter the From date in column I and the To date in column J. Use any combination of global and custom dates, as long as each row has a From and To date in either field. If global dates are not present and custom dates are not specified for a row, the macro will make no changes to that row.

t

To obtain dividends through today (or yesterday's close), enter "t" (without quotes) in the To column (J) of a row.

t-n

To obtain dividends from n months ago through today, enter "t-n" (without quotes) in the From column (I) of a row, where n is an integer. Also enter "t" in the To column (J). For example, "t-6" in the From column and "t" in the To column will download dividends from the beginning of the sixth previous month through today (or yesterday's close) and will eliminate anything before that. Global dates or an "f" in the Keep column have no effect.

f

An "f" (without quotes) in the Keep column (M) will cause the macro to download the full range specified. This should be done the first time a new symbol is added; then the "f"s can be deleted, to speed up the downloading of future dividends. Without an "f", the macro will retrieve only the last two months for that symbol in the specified range.

An "f" in the Keep column has no effect when the From date is "t-n" the macro will always retrieve only the last n months and delete anything earlier.

y

To cause the macro to not update the dividends in a row, enter "y" (without quotes) in the Keep column (M) of that row.

#

Column K shows the number of dividends paid during the specified date range.

Total $

Column L shows the total amount paid during the specified date range.

Custom columns

Columns E-H can be used for additional calculations for example: 

  • The number of dividends for the year in column E (usually 1, 4, or 12; these must be entered manually)

  • The amount of each dividend in column F. The formula in column F takes the most recent dividend in the past 12 months. Copy the formula to any other rows where this is desired. Note: If the dividend for a symbol fluctuates from one period to the next, the values shown here and in the next two columns, which are based on it, will not be accurate.

  • The amount paid annually (E * F) in column G.

  • The yield (G/price from the Daily tab) in column H 

These columns can be hidden if not needed.

Note: If you add a dividend-paying security between the ex-date and the end of the month, a dividend may appear for that month, even if you didn't receive it. To avoid having this affect any calculations about the dividend amount you've received for that symbol, wait two more months, then delete the initial dividend. (You can change the font or background color of that cell as a reminder during this time.) Since the macro ordinarily downloads only the most recent two months, it will not reload values earlier than that unless you place an "f" in the Keep column (this does not apply where "t-n" is used in the From column; see above).


To update your file to a new version:

  1. Copy the list of symbols from column D and anything in columns A-C and paste them into each sheet of the new file.  

  2. Daily tab: Copy the authentication token from D3 and paste it into the new file. Click the Daily and Names buttons to update.

  3. Historical tab: Copy any archived columns, starting with row 5, and paste them into the new file. Set the Frequency, Close type, and From and To dates. Click the Hist button to update.

  4. Dividends tab: Copy the From and To date columns, starting with row 7, and paste them into the new file. Enter global From and To dates in I6 and J6 if desired. Copy and paste anything in columns E-H. In the Keep column (M) of the new file, enter "f" (without quotes) for each symbol. Click the Divs button to update. Delete the "f"s from the Keep column (unless you want to download the full range every time). Copy any archived rows (with "y" in the Keep column), starting with column M and dividends to the right of it, and paste them into the new file.

  5. Save the file.


   Changelog

Version

Changes

Date
2.10 Dividends tab: bug fix; use any combination of global and custom dates; in the Keep column; "f" is not supported for rows with "t-n". 4/18/20

Contact the developer about custom modifications or new Excel projects at ajold.projects {at} gmail {dot} com.

Get Ultimate Excel Stock Quotes Downloader now, for Excel 2007 and later. This software is free to download and use.* Also requires free registration at Tiingo.com to use their API.

If you like this software, please consider making a contribution to the developer.

This website 2017 Ultimate Excel Stock Quotes Downloader. Use without permission is prohibited.

*Ultimate Excel Stock Quotes Downloader is released under a FreeBSD license. It is free for personal or commercial use with attribution to www.ultimatestockdownloader.com but may not be sold and may not be given away as part of a software package or bundle. The FreeBSD license appears on the Information tab of the software. For any copyright questions, contact 
info {at} ultimatestockdownloader {dot} com.