Vanguard CSV file of fund performance history
Vanguard CSV file of fund performance history
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 (commaseparated) file, but I'm sure I could get other file formats to work, too.

 Posts: 693
 Joined: Sat Feb 21, 2015 2:29 pm
Re: Vanguard CSV file of fund performance history
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/ ... storhome/#/
 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
Re: Vanguard CSV file of fund performance history
finance.yahoo.com offers historical price downloads
Re: Vanguard CSV file of fund performance history
Anyway to get the distributions since inception?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/ ... storhome/#/
 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
"Reverify our range to target ... one ping only."
Re: Vanguard CSV file of fund performance history
In the past, I've used the macro found here http://www.mathfinance.cn/downloadmult ... oofinance to download Yahoo price data for multiple symbols into Excel.
Re: Vanguard CSV file of fund performance history
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
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
....... Suggested format for Asking Portfolio Questions (edit original post)

 Posts: 693
 Joined: Sat Feb 21, 2015 2:29 pm
Re: Vanguard CSV file of fund performance history
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.One Ping wrote:Anyway to get the distributions since inception?
Re: Vanguard CSV file of fund performance history
Ah, that's what I was afraid of ...FactualFran wrote:One Ping wrote: ... and there are instances where the data from Yahoo is not the same as the data from Vanguard.
"Reverify our range to target ... one ping only."
Re: Vanguard CSV file of fund performance history
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.FactualFran wrote:Not as far as I know.One Ping wrote:Anyway to get the distributions since inception?
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 monthend prices for month 2 and month 1, and D is the distribution amount for month 2.
For the nondistribution months total return is just price return, P2/P1  1, and the calculated returns agree exactly with PV monthly returnsat 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
....... Suggested format for Asking Portfolio Questions (edit original post)
Re: Vanguard CSV file of fund performance history
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.
"Reverify our range to target ... one ping only."
Re: Vanguard CSV file of fund performance history
You can get 10 years of distributions from the institutional Vanguard sitesame page from which you can download prices since inception. For VTSMX, for example, since 6/25/2007.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.
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
....... Suggested format for Asking Portfolio Questions (edit original post)
Re: Vanguard CSV file of fund performance history
Thanks.
"Reverify our range to target ... one ping only."

 Posts: 693
 Joined: Sat Feb 21, 2015 2:29 pm
Re: Vanguard CSV file of fund performance history
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.Kevin M wrote: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.FactualFran wrote:Not as far as I know.One Ping wrote:Anyway to get the distributions since inception?
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.
Re: Vanguard CSV file of fund performance history
You can calculate either one. D in the formula I showed above is the pershare distribution amount. Solving for D:FactualFran wrote: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.Kevin M wrote: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.FactualFran wrote:Not as far as I know.One Ping wrote:Anyway to get the distributions since inception?
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).
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.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.
Kevin
....... Suggested format for Asking Portfolio Questions (edit original post)
Re: Vanguard CSV file of fund performance history
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: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 monthend prices for month 2 and month 1, and D is the distribution amount for month 2.
R = (1 + D/Pr) * P2 / P1 1
where Pr is the reinvestment price, so D/Pr is the fractional share purchased with pershare 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
....... Suggested format for Asking Portfolio Questions (edit original post)
Re: Vanguard CSV file of fund performance history
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.
One Ping
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.
One Ping
"Reverify our range to target ... one ping only."

 Posts: 693
 Joined: Sat Feb 21, 2015 2:29 pm
Re: Vanguard CSV file of fund performance history
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.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 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.