Quote grabbing spreadsheet?

I’m hoping someone would be willing to share an Excel spreadsheet that can grab a handful of quotes.

Each quarter-end I review the quarter and YTD performance for the basic indices and respective ETFs, as well as the JP Morgan portfolio sector ETFs. And, due to dividends, I currently have to go back and reenter the previous December 31st values, and also have to check the previous quarter value to see if it has changed. I have about 20 tickers.

It sure would be nice to have an automated way to do this.

TIA,
Tails

1 Like

I don’t have Excel anymore (so, can’t tell if this works) but found this documentation: Get a stock quote
https://support.microsoft.com/en-us/office/get-a-stock-quote…

Google Sheets has something similar: https://www.howtogeek.com/449743/how-to-track-stocks-with-go…

  • zol
5 Likes

Google sheets can do it.
Also, with some fancy footwork it can sometimes grab data from other sites, like Fidelity, Tiingo, etc.

1 Like

I don’t have Excel anymore (so, can’t tell if this works) but found this documentation: Get a stock quote
https://support.microsoft.com/en-us/office/get-a-stock-quote…

Google Sheets has something similar: https://www.howtogeek.com/449743/how-to-track-stocks-with-go…

I am in need of Adjusted Closing price, which account for dividends. I played with the listed functions (thanks zol!), but they only grab actual closing prices, and there is no option to grab Adjusted Closing prices.

Any other leads?, or ideas?

Tails

https://finance.yahoo.com/quote/SPY/history?period1=16104096…

One ticker at a time is how ya do it on yahoo.

GD_

1 Like

I am in need of Adjusted Closing price, which account for dividends. I played with the listed functions (thanks zol!), but they only grab actual closing prices, and there is no option to grab Adjusted Closing prices.

Nobody does that right anyway.
All the computations assume you reinvest on the ex-date. But you can’t have, because you don;t have the money yet.

Ex: AIO went ex-div on Dec 23 and paid on Jan 10.

AIO closed on Dec 23 at 25.02. Opened at 24.25. [*]

But my dividend got reinvested on Jan 10 at 25.0123.
OHLC on Jan 10 was 25.06 25.78 24.73 25.23
Note that my reinvestment price was not at open or close, but was somewhere between hi and lo.

One broker said "Dividends will then be reinvested starting at market open (9:30 AM ET) on the trading day after the dividend pay date. Because it typically takes some time to process the reinvestment orders, your dividend may not be reinvested right at market open."

For historical adjusted close, the only place I know of where you can get it is Yahoo. And you have to download it.
But, again, Yahoo’s adjusted close is wrong. You can see that, as the close and adj close are the same on the paid date and differ starting on the ex-date.

Usually historical dates for dividends are the ex-date, not the paid-date.

============
[*] Ideally, you would reinvest the dividend at the open, because that’s the price that best accounts for the paid out dividend. That’s a really good time to make a new investment.

4 Likes

What Ray said - it’s easy: =GoogleFinance(a1) simple level, but it is simply Yahoo Finance and does not include dividends. I don’t have too many individual investments that pay dividends, so for those I just download from YF myself and calculate the adjusted close.

For mutual funds (maybe ETFs?) GF provides historical distributions for the last n periods from that googlefinance api. Then adjust the close yourself in the spreadsheet.

Barcharts API does this as well for a reasonable price.

1 Like

I use this add-on in Excel:

https://michael-saunders.com/stocksapp/pages/info.html#howto…

Works in older versions (I’m using 2016).

More or less real time stock & ETF quotes.
You’d have to do your dividend adjustment yourself.

2 Likes

You’d have to do your dividend adjustment yourself.

One of the sites I visit covers CEFs and ETFs. The way he handles computing returns/gains is to add the dividends for the period to the ending price. He calls this “dividends added in, not reinvested”.
This works okay for short(ish) periods, like monthly or quarterly.

Going back for several years is problematic.

Regardless, however dividends are handled, it is wrong. It is not possible to do it right, because: a) you don’t know the dividend pay date, only the ex-date and b) it is not possible to know the exact price the reinvestment will get.

Since it is not possible to be accurate, just pick a method that is easy.

3 Likes

Tails,

I had saved a link from an old post that might solve your problem.
For adjusted prices, keep the adjusted prices box checked.

http://finance.jasonstrimpel.com/bulk-stock-download/

The first run returns a yellow box at the top. Click the download link to download the
spreadsheet.

At least it meets Ray’s condition, ‘Since it is not possible to be accurate, just pick a method that is easy.’ :slight_smile:

rrjjgg

2 Likes

As others have mentioned, Yahoo Finance is one of the few free sources of adjusted close prices. After several updates to their site in recent years, I believe the only way to automate the process now is through some form of scraping. I have written a short Google script function to do this but am not sure if it is wise to share in case it leads to overuse which may in turn lead to Yahoo adding further “improvements” to their site.

The basic idea is that it fetches a portion of the historical prices and parses it to find the number which equates to the adjusted close price for a given ticker on a given date (or most recent previous date that it traded if the date you enter is a non-trading date).

To use it in Google sheets, you simply enter a formula like this in the desired cell.

=yahooAdjClose(D35,F4,H4)

In this case:

  • Cell D35 holds the ticker symbol
  • F4 contains the date for which you want the price. You can set it to point to the same field for all tickers.
  • H4 is a refresh cell. The idea behind this is that Google sheets will update the prices when you open the sheet, but if you want to force it to refresh, you just change the value in cell H4. Again you can set it to point to the same field for all tickers if you want to just change one field for all your prices to refresh. In the case of a historical price, this refresh option is probably a bit redundant as it will refresh if you change either of the other fields too. I think I just copied this across from another function.

I wrote a similar one for Excel but you need to also build some VBA macros around it.

StevnFool

4 Likes

For historical adjusted close, the only place I know of where you can get it is Yahoo. And you have to download it.
But, again, Yahoo’s adjusted close is wrong. You can see that, as the close and adj close are the same on the paid date and differ starting on the ex-date.

Usually historical dates for dividends are the ex-date, not the paid-date.

You may be reinvesting your dividend on the day after the pay date, but that doesn’t mean your calculation method correctly reflects total return. The stock price drops at the ex date because you own the dividend as of that date. You just happen to be lending it to the paying company until the pay date. Using your method, if you charted the price from before the ex until after the pay date, you’d get an artificial price drop on the ex date and an artificial pop on the pay date. Anyone who bought or sold within that period would get an incorrect return.

Elan

1 Like

About 4 years ago after the last Y! change I reworked the Visual Basic code in Excel to download stock prices. See this post.

https://discussion.fool.com/updated-excel-y-macro-32887565.aspx

It does not download historical data, but I suspect it could be modified to do so. I shared the spreadsheet in the above link.

Of course, this could be done easily enough in a programming language. Say Python and dataframes (Pandas). I regularly download adjusted closes this way.

John

2 Likes
For any Windows users, here is a PowerShell solution to obtain historical adjusted close prices from 
Yahoo Finance. This has been tested on Windows 10.

Instructions:
1.  Copy the text at the bottom of this message into a text file and save and rename it
    to "Retriever.ps1".
2.  Create an text file called "Tickers.txt" in the same folder where you have saved Retriever.ps1.
3.  Copy your list of tickers into "Tickers.txt" - one ticker per line with nothing else in the file.
3.  To get the quotes, right-click on "Retriever.ps1" and click on "Run with PowerShell".
4.  If you get a message about Execution Policy (you probably will the first time), answer Y.
5.  Enter the date when requested.
6.  It should produce a list of tickers and adjusted close prices (comma separated) for the date
    entered which you can copy and pasted back into Excel.

Sample input/output:

<i>Adjusted Close Stock Price Retriever from Yahoo Finance.

Enter the date for which you want the prices: 11 Jan 22

Stock prices for close of: 11 January 2022
MSFT,314.9800109863281
JNJ,171.25
SLB,36.47999954223633
BRK-B,319.79998779296875
Press Enter to continue...:</i>

Enjoy.
StevnFool

----- Copy all text below this line ----
Write-Host "Adjusted Close Stock Price Retriever from Yahoo Finance."
Write-Host
$DateString = Read-Host Enter the date for which you want the prices
Write-Host
$PriceDate = [DateTime]::Parse($DateString)

$period2 = ([DateTimeOffset]$PriceDate).ToUnixTimeSeconds() +86400 # Adds one day (86400s) to align with Yahoo
$period1 = $period2 - 604800 # A week earlier so if there is mising data, you can use the latest.

$tickers = Get-Content tickers.txt

$DateOutput = Get-Date $PriceDate -Format D
Write-Host "Stock prices for close of: $DateOutput"

Foreach ($ticker in $tickers)
{
  $url = "[https://finance.yahoo.com/quote/"](https://finance.yahoo.com/quote/") + $ticker + "/history?period1=" + $period1 + "&period2=" + $period2 + "&interval=1d&filter=history&frequency=1d"
  $Response = Invoke-RestMethod $url
  $text = $Response -split 'adjclose":'
  $text = $text[1] -split '}'
  $ticker + "," + $text[0]
}
Pause
16 Likes