Vanguard CSV files for account transactions ?

Questions on how we spend our money and our time - consumer goods and services, home and vehicle, leisure and recreational activities
Post Reply
Topic Author
JohnFiscal
Posts: 787
Joined: Mon Jan 06, 2014 4:28 pm
Location: Florida

Vanguard CSV files for account transactions ?

Post by JohnFiscal » Thu Nov 14, 2019 11:52 am

With Vanguard transitioning to the "new" "brokerage" account system I am wondering how this will affect my custom spreadsheet programming.

I download the account values and transactions using the CSV file format from the Vanguard site. I then have some custom programming to extract data and enter into my spreadsheets.

Does the "new" "brokerage" account system provide the ability to download to CSV file format?
If so, is the file format identical to that used by the "mutual fund" account system?

Thank you.

Silk McCue
Posts: 4160
Joined: Thu Feb 25, 2016 7:11 pm

Re: Vanguard CSV files for account transactions ?

Post by Silk McCue » Thu Nov 14, 2019 11:59 am

I just logged into VG to check for you as I am on the Brokerage account. Yes, as expected, an option to download a CSV file is available along with Quicken and Microsoft Money.

Cheers

Topic Author
JohnFiscal
Posts: 787
Joined: Mon Jan 06, 2014 4:28 pm
Location: Florida

Re: Vanguard CSV files for account transactions ?

Post by JohnFiscal » Thu Nov 14, 2019 1:32 pm

Silk McCue wrote:
Thu Nov 14, 2019 11:59 am
I just logged into VG to check for you as I am on the Brokerage account. Yes, as expected, an option to download a CSV file is available along with Quicken and Microsoft Money.

Cheers
Thank you. Much obliged!

I’ve asked Vanguard if the format is the same.

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

Re: Vanguard CSV files for account transactions ?

Post by Kevin M » Thu Nov 14, 2019 1:48 pm

JohnFiscal wrote:
Thu Nov 14, 2019 11:52 am
With Vanguard transitioning to the "new" "brokerage" account system I am wondering how this will affect my custom spreadsheet programming.

I download the account values and transactions using the CSV file format from the Vanguard site. I then have some custom programming to extract data and enter into my spreadsheets.

Does the "new" "brokerage" account system provide the ability to download to CSV file format?
If so, is the file format identical to that used by the "mutual fund" account system?

Thank you.
I assume you are talking about "upgrading" an account from the old mutual-fund-only platform to the "new" brokerage platform. I'll refer to these as "account type".

The accounts download functionality is independent of the account type. I have both mutual fund and brokerage account types, and they all get downloaded in one file if I select them all. However, the formatting within the file is different for brokerage accounts.

One big difference is that for mutual fund accounts, each fund has it's own account number, which is a combination of the main account number and the fund ID. After transitioning to a brokerage account, each fund no longer has its own account number--there is just the brokerage account number.

Your programming or scripting to extract data from the download file probably will have to change, since the fields are different.

For mutual fund accounts, these are the fields for the holdings:

Code: Select all

Fund Account Number	Fund Name	Price	Shares	Total Value
For a brokerage account, these are the fields for holdings:

Code: Select all

Account Number	Investment Name	Symbol	Shares	Share Price	Total Value
For mutual fund accounts, these are the fields for the transactions:

Code: Select all

Account Number	Trade Date	Process Date	Transaction Type	Transaction Description	Investment Name	Share Price	Shares	Gross Amount	Net Amount
For a brokerage account, these are the fields for the transactions:

Code: Select all

Trade Date	Settlement Date	Transaction Type	Transaction Description	Investment Name	Symbol	Shares	Share Price	Principal Amount	Commission Fees	Net Amount	Accrued Interest	Account Type
I have many portfolio spreadsheets for which I use the accounts download, but I don't work with the CSV file directly. Instead, I load the CSV file into a sheet, then use spreadsheet filtering to pull the relevant holdings data into a portfolio sheet. I don't do anything with the transaction data.

When an account is upgraded from mutual fund to brokerage, I must modify the spreadsheet filtering to work with the different fields for a brokerage download.

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

Topic Author
JohnFiscal
Posts: 787
Joined: Mon Jan 06, 2014 4:28 pm
Location: Florida

Re: Vanguard CSV files for account transactions ?

Post by JohnFiscal » Thu Nov 14, 2019 7:20 pm

wow! Thank you Kevin. This is great. Now I know what to expect. Maybe I won't even be discouraged having to re-program this stuff again.

FWIW, this programming, on which I've spent untold hours, has saved me at least as many untold hours entering transactions into my own ledgers kept in Excel. As well as the error-prone tedium of manual entry.

Only my wife's Vanguard account has been selected to be "converted" at this point. I wonder how it will work if they delay my assigned conversion for a year, how that'll affect the joint accounts. Oh well, guess I will find out!

Kevin M wrote:
Thu Nov 14, 2019 1:48 pm
JohnFiscal wrote:
Thu Nov 14, 2019 11:52 am
With Vanguard transitioning to the "new" "brokerage" account system I am wondering how this will affect my custom spreadsheet programming.

I download the account values and transactions using the CSV file format from the Vanguard site. I then have some custom programming to extract data and enter into my spreadsheets.

Does the "new" "brokerage" account system provide the ability to download to CSV file format?
If so, is the file format identical to that used by the "mutual fund" account system?

Thank you.
I assume you are talking about "upgrading" an account from the old mutual-fund-only platform to the "new" brokerage platform. I'll refer to these as "account type".

The accounts download functionality is independent of the account type. I have both mutual fund and brokerage account types, and they all get downloaded in one file if I select them all. However, the formatting within the file is different for brokerage accounts.

One big difference is that for mutual fund accounts, each fund has it's own account number, which is a combination of the main account number and the fund ID. After transitioning to a brokerage account, each fund no longer has its own account number--there is just the brokerage account number.

Your programming or scripting to extract data from the download file probably will have to change, since the fields are different.

...
Kevin

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

Re: Vanguard CSV files for account transactions ?

Post by Kevin M » Fri Nov 15, 2019 3:46 pm

JohnFiscal wrote:
Thu Nov 14, 2019 7:20 pm
wow! Thank you Kevin. This is great. Now I know what to expect. Maybe I won't even be discouraged having to re-program this stuff again.

FWIW, this programming, on which I've spent untold hours, has saved me at least as many untold hours entering transactions into my own ledgers kept in Excel. As well as the error-prone tedium of manual entry.

Only my wife's Vanguard account has been selected to be "converted" at this point. I wonder how it will work if they delay my assigned conversion for a year, how that'll affect the joint accounts. Oh well, guess I will find out!
There is an alternative to manual entry or programming: import the download file into a sheet in your Excel ledger, then use spreadsheet functions to get into the ledger sheet(s). Unfortunately, I don't think Excel has a FILTER function like Google Sheets does, and I use this extensively.

By "how it will work", if you mean the downloads, you'll get what I described--two different file formats in one file. It's really no big deal. I actually find the brokerage format to be more usable--e.g., they include the tickers.

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

User avatar
bertilak
Posts: 7202
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Vanguard CSV files for account transactions ?

Post by bertilak » Fri Nov 15, 2019 4:42 pm

JohnFiscal wrote:
Thu Nov 14, 2019 11:52 am
Does the "new" "brokerage" account system provide the ability to download to CSV file format?
Yes. I use it all the time to keep my spreadsheet up to date.

Can't say anything about the old MF-type account.
May neither drought nor rain nor blizzard disturb the joy juice in your gizzard. -- Squire Omar Barker (aka S.O.B.), the Cowboy Poet

Topic Author
JohnFiscal
Posts: 787
Joined: Mon Jan 06, 2014 4:28 pm
Location: Florida

Re: Vanguard CSV files for account transactions ?

Post by JohnFiscal » Fri Nov 15, 2019 7:22 pm

Vanguard sent me a very informative reply to my inquiry. CSV downloads still available (as reported by others) and there are some slight differences in formatting of the fields.

So any custom programming to extract data from the new files will have to be modified. Probably not too bad.

From Vanguard:

The CSV formats are not perfectly identical, but they are very similar. The
differences between the two CSV files is due to the account types. The
brokerage CSV file actually includes more data. Below, I have listed what
is included on your current CSV format versus the brokerage version. Items
with an ( * ) are different than the other formatting.

Mutual Fund CSV includes the following:

Current price data:
-- Fund/Account number (Column A).
-- Fund Name (Column B).
-- Price (Columns C)
-- Shares (Columns D)
-- Total Value (Columns E)

Transaction history:
-- Account Number (Column A)
-- Trade Date (Column B)
-- Process Date (Column C)
-- Transaction Type (Column D)
-- Transaction Description (Column E)
-- Investment Name (Column F)
-- Share Price (Column G)
-- Shares (Column H)
-- Gross Amount (Column I)
-- Net Amount (Column J)

Brokerage CSV includes the following:

Current price data:
-- Account Number (Column A)
-- Investment Name (Column B)
-- Symbol (Column C)*
-- Shares (Column D)
-- Share Price (Column E)
-- Total Value (Column F)

Transaction history:
-- Account Number (Column A)
-- Trade Date (Column B)
-- Settlement Date (Column C)*
-- Transaction Type (Column D)
-- Transaction Description (Column E)
-- Investment Name (Column F)
-- Symbol (Column G)*
-- Shares (Column H)
-- Share Price (Column I)
-- Principal Amount (Column J)*
-- Commission Fees (Column K)*
-- Net Amount (Column L)
-- Accrued Interest (Column M)*
-- Account Type (Column N)*

Please note: The brokerage version swaps the "Shares" and "Share Price"
columns in the transaction history section.

Topic Author
JohnFiscal
Posts: 787
Joined: Mon Jan 06, 2014 4:28 pm
Location: Florida

Re: Vanguard CSV files for account transactions ?

Post by JohnFiscal » Fri Nov 15, 2019 7:39 pm

Kevin M wrote:
Fri Nov 15, 2019 3:46 pm
JohnFiscal wrote:
Thu Nov 14, 2019 7:20 pm
wow! Thank you Kevin. This is great. Now I know what to expect. Maybe I won't even be discouraged having to re-program this stuff again.

FWIW, this programming, on which I've spent untold hours, has saved me at least as many untold hours entering transactions into my own ledgers kept in Excel. As well as the error-prone tedium of manual entry.

Only my wife's Vanguard account has been selected to be "converted" at this point. I wonder how it will work if they delay my assigned conversion for a year, how that'll affect the joint accounts. Oh well, guess I will find out!
There is an alternative to manual entry or programming: import the download file into a sheet in your Excel ledger, then use spreadsheet functions to get into the ledger sheet(s). Unfortunately, I don't think Excel has a FILTER function like Google Sheets does, and I use this extensively.

By "how it will work", if you mean the downloads, you'll get what I described--two different file formats in one file. It's really no big deal. I actually find the brokerage format to be more usable--e.g., they include the tickers.

Kevin
I know that Excel has some extensive database facilities, I have used some of them over the years (in distant past). I've long wanted to monkey around with databases in Excel and even real ones (note to self: you now have Access). But my needs for the Vanguard CSV were to extract the data and push them into a number of individual ledgers (worksheets) for each account/type/ownership (eg: between my wife and I we have "joint", and our individual IRAs, Roths, Rollover IRAs, Inherited IRAs...so there are like so many sheets to keep up...whether I need to do so is another question, but I want to, so I do)

Using VBA lets me program the data extraction (of course, the "table" format can thus never change, else the data get hosed). And I actually do copy the two tables into a "work" sheets in the workbook where the VBA then carries on and extracts/punts the data. This was developed over a lot of time but has saved me lots of time and no end of grief trying to get data into the right place.
(editing to add, that once I have the CSV file open in Excel it takes me only a moment to click the "run" button and a few moments more to update all Vanguard data...transactions, share prices, etc. It probably takes me less than 1-2 minutes to log into Vanguard, download the CSV file, click "run" and get updated...really nice when there are all the monthly transactions for earning/distributions)

Again, thank you Kevin.

User avatar
bertilak
Posts: 7202
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Vanguard CSV files for account transactions ?

Post by bertilak » Sat Nov 16, 2019 10:43 am

I dump the CSV download into a sheet for raw data and select from that to put selected data into other sheets. I use a combination of two spreadsheet functions to get the data: INDEX and MATCH. These are found at ...
  • Formulas tab, Lookup & Reference
Here is a formula to grab a price quote given a ticker:
  • {=INDEX(quote,MATCH($B3,ticker,0))}
Where "quote" and "ticker" are named data ranges in the raw data sheet. In this example the list of tickers is hard coded in column B and the example ticker is in row 3. Note the curly braces. They indicate an array formula and are created by using CTRl-SHIFT-ENTER instead of just ENTER when typing in the formula. (Don't type the curly braces.)

Similar formulas can be used to get other fields, like number of shares. If you need to deal with multiple account numbers use the ampersand (&) to indicate multiple selection criteria. Getting number of shares for a specific ticker in a specific account may look like this:
  • {=INDEX(shares,MATCH($B3&$A$1,ticker&account,0))}
Where $A$1 is the account number, presumably having a bunch of tickers listed on column B.
May neither drought nor rain nor blizzard disturb the joy juice in your gizzard. -- Squire Omar Barker (aka S.O.B.), the Cowboy Poet

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

Re: Vanguard CSV files for account transactions ?

Post by Kevin M » Sat Nov 16, 2019 4:25 pm

bertilak wrote:
Sat Nov 16, 2019 10:43 am
I dump the CSV download into a sheet for raw data and select from that to put selected data into other sheets. I use a combination of two spreadsheet functions to get the data: INDEX and MATCH. These are found at ...
  • Formulas tab, Lookup & Reference
Here is a formula to grab a price quote given a ticker:
  • {=INDEX(quote,MATCH($B3,ticker,0))}
Where "quote" and "ticker" are named data ranges in the raw data sheet. In this example the list of tickers is hard coded in column B and the example ticker is in row 3. Note the curly braces. They indicate an array formula and are created by using CTRl-SHIFT-ENTER instead of just ENTER when typing in the formula. (Don't type the curly braces.)

Similar formulas can be used to get other fields, like number of shares. If you need to deal with multiple account numbers use the ampersand (&) to indicate multiple selection criteria. Getting number of shares for a specific ticker in a specific account may look like this:
  • {=INDEX(shares,MATCH($B3&$A$1,ticker&account,0))}
Where $A$1 is the account number, presumably having a bunch of tickers listed on column B.
Looks like INDEX/MATCH provides an Excel way to do what can be done with FILTER in Google Sheets.

Wouldn't it be easier to use VLOOKUP when you're just looking for a single value, as in your first example?

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

User avatar
bertilak
Posts: 7202
Joined: Tue Aug 02, 2011 5:23 pm
Location: East of the Pecos, West of the Mississippi

Re: Vanguard CSV files for account transactions ?

Post by bertilak » Sat Nov 16, 2019 4:36 pm

Kevin M wrote:
Sat Nov 16, 2019 4:25 pm
Wouldn't it be easier to use VLOOKUP when you're just looking for a single value, as in your first example?
Not sure. It's been a while since I came up with this.

There is a complication in that I have multiple accounts, many having some of the same assets (tickers). I'm not sure if VLOOKUP can handle that or if it needs special sorting. Using INDEX and MATCH can handle unsorted data as it comes in Vanguard's CSV file. I don't know if that's a big deal or not but I'm pretty sure it works best when I need to get the number of shares, which can be different for the same ticker in different accounts. What I have is consistent across both situations.
May neither drought nor rain nor blizzard disturb the joy juice in your gizzard. -- Squire Omar Barker (aka S.O.B.), the Cowboy Poet

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

Re: Vanguard CSV files for account transactions ?

Post by Kevin M » Sat Nov 16, 2019 4:39 pm

JohnFiscal wrote:
Fri Nov 15, 2019 7:39 pm
Kevin M wrote:
Fri Nov 15, 2019 3:46 pm
JohnFiscal wrote:
Thu Nov 14, 2019 7:20 pm
wow! Thank you Kevin. This is great. Now I know what to expect. Maybe I won't even be discouraged having to re-program this stuff again.

FWIW, this programming, on which I've spent untold hours, has saved me at least as many untold hours entering transactions into my own ledgers kept in Excel. As well as the error-prone tedium of manual entry.

Only my wife's Vanguard account has been selected to be "converted" at this point. I wonder how it will work if they delay my assigned conversion for a year, how that'll affect the joint accounts. Oh well, guess I will find out!
There is an alternative to manual entry or programming: import the download file into a sheet in your Excel ledger, then use spreadsheet functions to get into the ledger sheet(s). Unfortunately, I don't think Excel has a FILTER function like Google Sheets does, and I use this extensively.

By "how it will work", if you mean the downloads, you'll get what I described--two different file formats in one file. It's really no big deal. I actually find the brokerage format to be more usable--e.g., they include the tickers.

Kevin
I know that Excel has some extensive database facilities, I have used some of them over the years (in distant past). I've long wanted to monkey around with databases in Excel and even real ones (note to self: you now have Access).
I'm not really talking about database functionality, but rather just using spreadsheet functions to get the data from a sheet containing the download (raw data) into other sheets that are used for a particular purpose--in my case portfolio sheets, in your case what you're calling ledgers.

I use FILTER, QUERY, and VLOOKUP in Google Sheets, but bertilak gave examples of doing something similar in Excel.
JohnFiscal wrote:
Fri Nov 15, 2019 7:39 pm
But my needs for the Vanguard CSV were to extract the data and push them into a number of individual ledgers (worksheets) for each account/type/ownership (eg: between my wife and I we have "joint", and our individual IRAs, Roths, Rollover IRAs, Inherited IRAs...so there are like so many sheets to keep up...whether I need to do so is another question, but I want to, so I do)
Right. I do the same thing with spreadsheet functions in the various worksheets to extract the relevant data from the download worksheet.

Actually what I do is a bit more complex, since I manage a number of family portfolios, each having its own spreadsheet, and each spreadsheet having multiple sheets. I used to download from Vanguard into a "Vanguard" sheet in each portfolio spreadsheet, but now I download my accounts and all accounts for which I have agent authority into a master spreadsheet, extract the holdings for each portfolio into its own sheet, then use IMPORTRANGE to import the data for a particular portfolio into a "Vanguard" sheet for that portfolio.
JohnFiscal wrote:
Fri Nov 15, 2019 7:39 pm
Using VBA lets me program the data extraction (of course, the "table" format can thus never change, else the data get hosed). And I actually do copy the two tables into a "work" sheets in the workbook where the VBA then carries on and extracts/punts the data. This was developed over a lot of time but has saved me lots of time and no end of grief trying to get data into the right place.
(editing to add, that once I have the CSV file open in Excel it takes me only a moment to click the "run" button and a few moments more to update all Vanguard data...transactions, share prices, etc. It probably takes me less than 1-2 minutes to log into Vanguard, download the CSV file, click "run" and get updated...really nice when there are all the monthly transactions for earning/distributions)
It's similarly easy using my process. Log in, download, import into a sheet in Google Sheets. That's it. Like your VBA program, the formulas to extract the data are already developed, and do all the extracting work.
JohnFiscal wrote:
Fri Nov 15, 2019 7:39 pm
Again, thank you Kevin.
You're welcome!

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

Post Reply