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.

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 will not change and 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 need to be done only 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. 

To obtain dividends through today (or yesterday's close), enter "t" (without quotes) in the To column (J). It is possible to have an overall range in I5 and J5 for symbols with nothing in columns I and J and custom dates in those columns that apply only to the specific row.

To obtain dividends from n months ago through today, enter "t-n" (without quotes) in the From column (I), where n is an integer. Also enter "t" in the To column (J). For example, "t-1" in the From column and "t" in the To column will download dividends from the beginning of the previous month through today. To show only those dividends for that symbol, enter "f" in the Keep column (M); otherwise, the macro will download all dividends from the From date in I6 (see below).

For custom dates in each row, the macro will retrieve dividends for the range specified, but only the last two periods. This eliminates the need to retrieve the full range every time dividends are downloaded. To retrieve the full range, enter "f" (without quotes) in the Keep column (M) for each symbol. (It will be necessary to do this the first time the macro runs; then the "f"s can be deleted, to speed up the downloading of future dividends.)

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

Columns E-H and K-L can be used for additional calculations — for example, the number of dividends for the year in column E (usually 4 or 12), the amount of each dividend in column F, the amount paid annually (E*F) in column G, and the yield (G/price from the Daily tab) in column H. It can also show the number of dividends paid so far in column K and the total amount paid in column L, as in the example. 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 period, a dividend may appear for that period, 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 periods, 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 periods, it will not reload values earlier than that unless you place an "f" in the Keep column.