Vanguard CSV file of fund performance history

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
MrSnrub
Posts: 6
Joined: Thu Mar 02, 2017 3:51 pm

Vanguard CSV file of fund performance history

Post by MrSnrub » Tue May 02, 2017 7:56 pm

Does anybody know if Vanguard's website offers a download of share price history? I want to write an Excel macro that automatically downloads new share prices. I would prefer a csv (comma-separated) file, but I'm sure I could get other file formats to work, too.

FactualFran
Posts: 613
Joined: Sat Feb 21, 2015 2:29 pm

Re: Vanguard CSV file of fund performance history

Post by FactualFran » Wed May 03, 2017 3:59 pm

The complete price history of Vanguard funds are available through the Vanguard web site for Institutional Investors. Do the following
  • Open https://institutional.vanguard.com/web/ ... stor-home/#/
  • Click on the search icon (magnifiying glass image) near the top right corner
  • Enter the fund name or symbol in the search area
  • Click on the link of the fund
  • Click on the Price & Distribuions tab
  • Click on the Since Inception link in the Historial Prices section

livesoft
Posts: 60419
Joined: Thu Mar 01, 2007 8:00 pm

Re: Vanguard CSV file of fund performance history

Post by livesoft » Wed May 03, 2017 4:04 pm

finance.yahoo.com offers historical price downloads
Wiki This signature message sponsored by sscritic: Learn to fish.

User avatar
One Ping
Posts: 350
Joined: Thu Sep 24, 2015 4:53 pm

Re: Vanguard CSV file of fund performance history

Post by One Ping » Wed May 03, 2017 5:48 pm

FactualFran wrote:The complete price history of Vanguard funds are available through the Vanguard web site for Institutional Investors. Do the following
  • Open https://institutional.vanguard.com/web/ ... stor-home/#/
  • Click on the search icon (magnifiying glass image) near the top right corner
  • Enter the fund name or symbol in the search area
  • Click on the link of the fund
  • Click on the Price & Distribuions tab
  • Click on the Since Inception link in the Historial Prices section
Anyway to get the distributions since inception?
"Re-verify our range to target ... one ping only."

GLState
Posts: 126
Joined: Wed Feb 15, 2017 10:38 am

Re: Vanguard CSV file of fund performance history

Post by GLState » Wed May 03, 2017 6:44 pm

In the past, I've used the macro found here http://www.mathfinance.cn/download-mult ... oo-finance to download Yahoo price data for multiple symbols into Excel.

User avatar
Kevin M
Posts: 9406
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Vanguard CSV file of fund performance history

Post by Kevin M » Thu May 04, 2017 11:44 am

Other alternatives.

You can get monthly returns from Portfolio Visualizer and download them to a CSV file. Backtest Portfolio Asset Allocation. These are total returns, so they include price change and distributions.

You can get daily prices since 2000 with the GoogleFinance function in Google Sheets: =GOOGLEFINANCE("VTSMX", "price", DATE(1992,1,1), TODAY(), "DAILY"). I tried start date 1992 to see how far back it went, and it's 1/3/2000. Of course the Vanguard source is better since you can get prices since inception, but GoogleFinance is convenient for prices since 2000 if using Google Sheets.

You could calculate price return from the price data and subtract from the PV total return data to get the distribution returns, which will include dividends and cap gain distributions.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

FactualFran
Posts: 613
Joined: Sat Feb 21, 2015 2:29 pm

Re: Vanguard CSV file of fund performance history

Post by FactualFran » Thu May 04, 2017 2:31 pm

One Ping wrote:Anyway to get the distributions since inception?
Not as far as I know. http://finance.yahoo.com has distributions, in addition to the prices as mentioned by livesoft. However, the history available there may not go all the way back to the inception date, the distribution per share is given as a total rather than as separate amounts for income and capital gain, and there are instances where the data from Yahoo is not the same as the data from Vanguard.

User avatar
One Ping
Posts: 350
Joined: Thu Sep 24, 2015 4:53 pm

Re: Vanguard CSV file of fund performance history

Post by One Ping » Thu May 04, 2017 3:35 pm

FactualFran wrote:
One Ping wrote: ... and there are instances where the data from Yahoo is not the same as the data from Vanguard.
Ah, that's what I was afraid of ...
"Re-verify our range to target ... one ping only."

User avatar
Kevin M
Posts: 9406
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Vanguard CSV file of fund performance history

Post by Kevin M » Thu May 04, 2017 5:21 pm

FactualFran wrote:
One Ping wrote:Anyway to get the distributions since inception?
Not as far as I know.
I mentioned a way to do it in the reply immediately before yours. Get total return data from Portfolio Visualizer, and get price data from Vanguard. Calculate monthly price return. Subtract price return from total return to get distribution return.

I tested this using the distribution data from Vanguard since 12/2015, and the calculated returns are within one basis point of the total return data from PV.

R = (P2 + D)/P1 -1

where R is total return for month 2, P2 and P1 are the month-end prices for month 2 and month 1, and D is the distribution amount for month 2.

For the non-distribution months total return is just price return, P2/P1 - 1, and the calculated returns agree exactly with PV monthly returns--at least from my spot checks. For the distribution months, the result of the calculated total return is within one basis point of the PV monthly return.

However, PV may get its return data from Yahoo, so you may find some discrepancies between PV returns and those published by Vanguard.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
One Ping
Posts: 350
Joined: Thu Sep 24, 2015 4:53 pm

Re: Vanguard CSV file of fund performance history

Post by One Ping » Thu May 04, 2017 5:46 pm

KevinM - Didn't mean to seem like I was ignoring your comment, I really wasn't. I just hoped to be able to get all the info from Vanguard. Your approach is indeed a viable way to do it.
"Re-verify our range to target ... one ping only."

User avatar
Kevin M
Posts: 9406
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Vanguard CSV file of fund performance history

Post by Kevin M » Thu May 04, 2017 6:35 pm

One Ping wrote:KevinM - Didn't mean to seem like I was ignoring your comment, I really wasn't. I just hoped to be able to get all the info from Vanguard. Your approach is indeed a viable way to do it.
You can get 10 years of distributions from the institutional Vanguard site--same page from which you can download prices since inception. For VTSMX, for example, since 6/25/2007.

I see that Portfolio Visualizer lists http://www.csidata.com as the data source for US mutual funds and ETFs, so maybe it's more accurate than Yahoo if you want to go back further than 10 years.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
One Ping
Posts: 350
Joined: Thu Sep 24, 2015 4:53 pm

Re: Vanguard CSV file of fund performance history

Post by One Ping » Thu May 04, 2017 10:43 pm

Thanks. :beer
"Re-verify our range to target ... one ping only."

FactualFran
Posts: 613
Joined: Sat Feb 21, 2015 2:29 pm

Re: Vanguard CSV file of fund performance history

Post by FactualFran » Fri May 05, 2017 1:13 pm

Kevin M wrote:
FactualFran wrote:
One Ping wrote:Anyway to get the distributions since inception?
Not as far as I know.
I mentioned a way to do it in the reply immediately before yours. Get total return data from Portfolio Visualizer, and get price data from Vanguard. Calculate monthly price return. Subtract price return from total return to get distribution return.
It depends on what one means by distribution. To you it is a return. To me it is an amount per share that may be automatically reinvested at the NAV on the distribution date.

The request was for the distributions since inception. Distribution returns can be calculated using Portfolio Visualizer only as far back as it supports: from the start of 1986. The inception date of the often mentioned Vanguard Index 500 fund (VFINX) was in 1976. Yahoo has the distribution data only back to the start of 1980.

User avatar
Kevin M
Posts: 9406
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Vanguard CSV file of fund performance history

Post by Kevin M » Fri May 05, 2017 7:36 pm

FactualFran wrote:
Kevin M wrote:
FactualFran wrote:
One Ping wrote:Anyway to get the distributions since inception?
Not as far as I know.
I mentioned a way to do it in the reply immediately before yours. Get total return data from Portfolio Visualizer, and get price data from Vanguard. Calculate monthly price return. Subtract price return from total return to get distribution return.
It depends on what one means by distribution. To you it is a return. To me it is an amount per share that may be automatically reinvested at the NAV on the distribution date.
You can calculate either one. D in the formula I showed above is the per-share distribution amount. Solving for D:

D = (R+1)*P1 - P2

Just as the total return calculated with the formula is close, but not perfect (+/-1 bp), compared what PV shows, the distribution calculated from the formula is not perfect, but close to the actual distribution (+/- $0.004).
The request was for the distributions since inception. Distribution returns can be calculated using Portfolio Visualizer only as far back as it supports: from the start of 1986. The inception date of the often mentioned Vanguard Index 500 fund (VFINX) was in 1976. Yahoo has the distribution data only back to the start of 1980.
Sure, not for all funds, but for many funds. For example, I used VTSMX, with inception on 4/27/1992, for testing the idea. Just contributing ideas that might help people get closer to what they're looking for.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
Kevin M
Posts: 9406
Joined: Mon Jun 29, 2009 3:24 pm
Contact:

Re: Vanguard CSV file of fund performance history

Post by Kevin M » Sat May 06, 2017 11:09 am

Kevin M wrote: I tested this using the distribution data from Vanguard since 12/2015, and the calculated returns are within one basis point of the total return data from PV.

R = (P2 + D)/P1 -1

where R is total return for month 2, P2 and P1 are the month-end prices for month 2 and month 1, and D is the distribution amount for month 2.
I figured out a different return formula that gives results that exactly match PV monthly returns to the precision provided (two decimal places). Instead of just adding the per share distribution amount to P2 in the numerator of the above formula, I calculate the fractional share purchased by the reinvested distribution, and incorporate that into the formula as follows:

R = (1 + D/Pr) * P2 / P1 -1

where Pr is the reinvestment price, so D/Pr is the fractional share purchased with per-share distribution D.

I compared R from this formula, using distribution data since June 2007, to monthly returns from PV.

There were a few small deltas in my first pass using prices retrieved by the GoogleFinance function, so I downloaded prices from Vanguard, and that eliminated all deltas;i.e., there are some discrepancies in the prices returned by GoogleFinance compared to those provided directly by Vanguard. All of this was using VTSMX.

Solving the equation for D, and calculating the distribution amount per share using return values (R) from PV gives results that match to within +/- $0.002. Average error is 0.000 and standard deviation of the errors is 0.001. The errors are due to PV providing R values only to two decimal places.

Of course an issue with this approach to get distribution amount is that you need to know the reinvestment date to get the reinvestment price (Pr). Reinvestment dates are fairly consistent, but there is some variation from year to year.

Kevin
Wiki ||.......|| Suggested format for Asking Portfolio Questions (edit original post)

User avatar
One Ping
Posts: 350
Joined: Thu Sep 24, 2015 4:53 pm

Re: Vanguard CSV file of fund performance history

Post by One Ping » Sat May 06, 2017 12:24 pm

Thanks all for the input on this.

What I'm really trying to get at (maybe poorly stated) are the components of Total Return: i.e., Growth (NAV increase/decrease) and Distributions (e.g., CG distributions and dividends).

Maybe it's not a very sophisticated way of looking at it, but it's simple enough me ... unless I'm missing something incredibly obvious, which is always a possibility. :oops:

One Ping
"Re-verify our range to target ... one ping only."

FactualFran
Posts: 613
Joined: Sat Feb 21, 2015 2:29 pm

Re: Vanguard CSV file of fund performance history

Post by FactualFran » Sat May 06, 2017 12:48 pm

One Ping wrote: What I'm really trying to get at (maybe poorly stated) are the components of Total Return: i.e., Growth (NAV increase/decrease) and Distributions (e.g., CG distributions and dividends).
The components of Total Return used by Vanguard, such as on the web page with the historical returns of the Vanguard 500 Index Fund, are Capital Return and Income Return.

The Capital Return consist of the changes in the NAV and the effect or reinvesting capital gain distributions. The capital gain distributions can be viewed as taxation practice rather than an investment issue. Mutual fund avoid being taxed on capital gains they realize by passing the gains to those who invest in the mutual fund.

The Income Return is the difference between the Total Return and the Capital Return. It is not exactly the distribution yield that one would get by taking the dividends (income distributions) in cash and automatically reinvesting the capital gain distributions. Taking distributions that way is a common option available with mutual funds.

Post Reply