|

|
|

|
|
 |
|
|
|
|
|
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.
|
|
|
|
|
|
|
|
|
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
|
|
|
|
|
|
|
|
-
Enter From and To dates in cells D3 and D4. To download a single date, you can enter just the To date.
-
Select Daily, Weekly, or Monthly prices
from the dropdown list in D1 and Actual or Adjusted prices from
the dropdown in D2.
-
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:
-
Copy
the list of symbols from column D and anything in columns
A-C and paste them into each sheet of the new file.
-
Daily
tab: Copy the authentication token from D3 and paste it
into the new file. Click the Daily and Names buttons to
update.
-
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.
-
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.
-
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. |
|
|
|
|