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

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.

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.

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.

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:

• https://api.tiingo.com/account/token
• https://api.tiingo.com/docs/general/overview
• https://api.tiingo.com/docs/general/connecting

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.

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

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.

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.

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

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.

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

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.

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

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.

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.

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.

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

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.

Changes