bogle.tools - help others with more CSV File Format examples from different Custodians

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

I'm trying to add support to bogle.tools/portfolio-review to help import as many CSV file formats from different custodians as possible. This will make it easier for people creating Portfolio Review posts (forum post about tool) or to track your portfolio with Portfolio Tracker

Currently Support Importing 10 different file types (updated 1/7/2024)

Code: Select all

Custodian        Importable File Type Example File      Github Issue # 
================ ========== ========= ================= ==============
Ameriprise       Yes        CSV       Ameriprise-*.csv
Edward Jones     Not yet    ???       need help getting!     <===============
ETrade           Yes        CSV       etrade*.csv
Fidelity         Yes        CSV       fidelity.csv
JPMorgan Chase   Yes        CSV       jpmorgan-chase.csv    
Merrill Edge     Yes        CSV       merrillEdge.csv
Morgan Stanley   Yes        XLSX      not published
Public.com       No         CSV       not published      78 - closed as Not Planned
Raymond-James    Not yet    ???       need help getting!     <===============
Schwab           Yes        CSV       schawb.csv
TD Ameritrade    No         XLS       not published      80 - closed as Not Planned
T Rowe Price     Yes        CSV       t-rowe-price.csv
Wells Fargo      Not yet    ???       need help getting!     <===============
Vanguard         Yes        CSV       vanguard.csv
Vanguard (old)   Yes        CSV       vanguard-orig.csv 

Others           Not yet    help?     Email to suggestions@bogle.tools
                                      or send via private message
                                      (anonymize if you'd like)
Legend:
Example File - file name of shared example file from: https://github.com/bogle-tools/financia ... custodians
GitHub Issue # - see current status at https://github.com/bogle-tools/site/issues (or that URL plus '/#' (with # be the Github Issue # from table)

My process:
1) somebody wants to automatically import their portfolio data into my tool that helps create portfolio review posts: https://bogle.tools/portfolio-review (forum post about tool)
2) when they get to the Retirement Assets step: bogle.tools/portfolio-review/retirement-assets, they find a web page with:
Current Retirement Assets:

Portfolio Size: $0

[➕ Account ] to manually enter another Account's info.

[ Import Accounts ] to import Account info from a CSV/XLSX file.

3) If they click the "Import CSV" button, it shows:
Download portfolio details from custodian website, and then "choose file", to import:

[ Choose File ] No file chosen

Currently supports importing Fidelity, Schwab, or Vanguard CSV files and Morgan Stanley XLSX files
If your custodian's format isn't supported yet, we're happy to try to add support. If you can share your custodian's downloaded CSV file format (feel free to anonymize/change the data), please email it to suggestions@bogle.tools with details on what custodian it is from.
CAUTION: only share personal info (like these files) to apps worthy of your trust!
4) When I get access to a new file format, I work to add support for importing that format for bogle.tools

5) When done, I change the data in the CSV file, and save it for testing later in a public github repo: https://github.com/bogle-tools/financia ... custodians

If there is anything about this process that could be improved, please let me know!
Thanks, Rob
Last edited by Rob Relyea on Tue Apr 02, 2024 4:58 pm, edited 23 times in total.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Would love to have your help to get:
  • Edward Jones CSV File
  • Ameriprise CSV File
...and others

This will help people who are discovering bogleheads investment philosophy and our community's Portfolio Reviews.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

I got access to an Ameriprise CSV file, but it doesn't have any stocks/mutual funds in it.
You'll notice I changed the account numbers and the investment details to anonymize the data.

I'd love to have somebody who has an Ameriprise account with stocks/mutual funds, to share the details of the section that their downloaded CSV file would show...likely similar to "Alternative Investments" section, but guessing isn't good in software...

Via bogleheads private message or email (suggestions@bogle.tools) would work well for me.

PortfolioPositions_2-13-2023.csv:
"All Accounts","ALL_ACCOUNTS"
As of 02/13/2023 03:30 PM ET
,
"Total Asset Value","Brokerage Day Change","Total Cash & Equivalents"
"$3,000.00","UNCH","$0.00"
,
"Alternative Investments"
"$3,000.00"
,
"Cash & Equivalents"
"Type","Account Name","Account Description","Mkt. Value"
"Cash","AMERIPRISE BROKERAGE","**** 1234 5 678","$0.00"
"Total Cash & Equivalents","","","$0.00"
,
"Alternative Investments"
"Symbol","Description","Account Name","Account Description","Quantity","Mkt. Price","Mkt. Value","As Of"
"","Alternative Investment #1","AMERIPRISE BROKERAGE","**** 1234 5 678","1,000.00","$3.000","$3,000.00","2/1/2023"
"Total Alternative Investments","Total Alternative Investments","","","","","$3,000.00",""
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Importing ETrade investment info may be helpful in this thread: viewtopic.php?p=7129848#p7129848

(Assuming I also enhance the Portfolio Review tool to help calculate AA…which I am planning to do at some point)
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Ok, ETrade CSV import is now supported by https://bogle.tools/portfolio/retirement-assets

This is in addition to the Fidelity, Schwab and Vanguard CSV files that were already supported.

Thanks for your help!

Note: the CSV file I was given only had one account...if it is possible to get a CSV file from etrade.com that has more than one account in it, it likely won't work. Please let me know...
sycamore
Posts: 6429
Joined: Tue May 08, 2018 12:06 pm

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by sycamore »

Rob, here's a Merrill Edge sample. I have my own spreadsheet for tracking my portfolio, but thanks for working on this project.

Code: Select all

"COB Date","Security #","Symbol","CUSIP #","Security Description","Account Nickname","Account Registration","Account #","Quantity","Price ($)","Value ($)","Unrealized Gain/Loss ($)","Unrealized Gain/Loss (%)","Cumulative Investment Return ($)","Cumulative Investment Return (%)","Accrued Interest ($)"
"2/17/2023","12345","FUNDX","123456789","MY FUND NAME","--","CMA-Edge","99X-12345","10,000","1.23","12,300.00","2,300.00","23.00","2,300","23.00","--"
I anonymized the data. Hopefully it's clear what's what. There's no "basis" column but I think it can be calculated by subtracting column L "Unrealized Gain/Loss ($)" from column K "Value ($)"

To generate the CSV, a Merrill Edge user should first sign into Edge.
Next, on the default page (at least it's the default page for me), click on the "Download info" link that's underneath the "Merrill Accounts Total" box. Or access it via the menu: Accounts tab -> Account Resources -> Download Account Data.

On the Download Account Data page:
step 1 is Choose category or account -- the default is All Accounts
step 2 is Choose application -- I picked "Spreadsheet and text" (other options are Quicken and MS Money)
step 3 is Choose Settings -- I picked "Holdings"
step 4 is Download -- I picked CSV

Let me know if you have any questions.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

sycamore wrote: Tue Feb 21, 2023 6:50 pm Rob, here's a Merrill Edge sample. I have my own spreadsheet for tracking my portfolio, but thanks for working on this project.

Code: Select all

"COB Date","Security #","Symbol","CUSIP #","Security Description","Account Nickname","Account Registration","Account #","Quantity","Price ($)","Value ($)","Unrealized Gain/Loss ($)","Unrealized Gain/Loss (%)","Cumulative Investment Return ($)","Cumulative Investment Return (%)","Accrued Interest ($)"
"2/17/2023","12345","FUNDX","123456789","MY FUND NAME","--","CMA-Edge","99X-12345","10,000","1.23","12,300.00","2,300.00","23.00","2,300","23.00","--"
Thanks and you're welcome!

I've updated https://bogle.tools/portfolio/retirement-assets to have support for importing Merrill Edge CSV files.

Note... I didn't test a real file... Please test and let me know how it works. I might need more details if I didn't get it right the first time.
placeholder
Posts: 8508
Joined: Tue Aug 06, 2013 12:43 pm

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by placeholder »

There are a few new custodians notably public.com that don't have account download but because they use apex clearing under the hood you can create a corresponding apex account and get a nice csv download of the information so right now mine has the following columns:

Code: Select all

Symbol,Description,Qty,Price,Market Value,Day Change
EFV,ISHARES TRUST ISHARES MSCI EAFE VALUE ETF,xxx,xxx,xxx,xxx
You can select the columns to display and the order of them left to right.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

placeholder wrote: Tue Feb 21, 2023 11:45 pm There are a few new custodians notably public.com that don't have account download but because they use apex clearing under the hood you can create a corresponding apex account and get a nice csv download of the information so right now mine has the following columns:

Code: Select all

Symbol,Description,Qty,Price,Market Value,Day Change
EFV,ISHARES TRUST ISHARES MSCI EAFE VALUE ETF,xxx,xxx,xxx,xxx
You can select the columns to display and the order of them left to right.
Thanks placeholder for the pointer to public/apex.
Adding that to my backlog with: https://github.com/bogle-tools/site/issues/78

Have never heard of public before, but I see they may be interesting because of Brokerage transfer bonuses.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Somebody reached out via email saying that they could get one Custodian import to work, but their Vanguard CSV file wasn't working.
One possibility, they said, is that they still have the Original Vanguard Mutal Fund Account...so I will work with them to see if we can figure out the difference in that CSV file format, so the importing logic can be made to work with it also.

Tracking this problem here: https://github.com/bogle-tools/site/issues/79
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

ETrade File - #2
I was submitted an ETrade file that was broken, even though I had another ETrade file that worked.
So I fixed the code to work for both.

Please let me know if I say that a format is supported, if it fails for you!!


TD Ameritrade
I was also submitted a TD Ameritrade file, but it was strange...so I opened an issue, and I will think about it a bit before I decide whether to parse it. I'd prefer if TD Ameritrade downloaded the file as a .CSV file.
TD Ameritrade file - csv format, but with .xls extension #80
sycamore
Posts: 6429
Joined: Tue May 08, 2018 12:06 pm

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by sycamore »

Rob Relyea wrote: Tue Feb 21, 2023 7:51 pm
sycamore wrote: Tue Feb 21, 2023 6:50 pm Rob, here's a Merrill Edge sample. I have my own spreadsheet for tracking my portfolio, but thanks for working on this project.

Code: Select all

"COB Date","Security #","Symbol","CUSIP #","Security Description","Account Nickname","Account Registration","Account #","Quantity","Price ($)","Value ($)","Unrealized Gain/Loss ($)","Unrealized Gain/Loss (%)","Cumulative Investment Return ($)","Cumulative Investment Return (%)","Accrued Interest ($)"
"2/17/2023","12345","FUNDX","123456789","MY FUND NAME","--","CMA-Edge","99X-12345","10,000","1.23","12,300.00","2,300.00","23.00","2,300","23.00","--"
Thanks and you're welcome!

I've updated https://bogle.tools/portfolio/retirement-assets to have support for importing Merrill Edge CSV files.

Note... I didn't test a real file... Please test and let me know how it works. I might need more details if I didn't get it right the first time.
Seems to work just fine. The sample I gave was based on a real file; I had just anonymized the data. Thanks again!
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Rob Relyea wrote: Wed Feb 22, 2023 9:00 am Somebody reached out via email saying that they could get one Custodian import to work, but their Vanguard CSV file wasn't working.
One possibility, they said, is that they still have the Original Vanguard Mutal Fund Account...so I will work with them to see if we can figure out the difference in that CSV file format, so the importing logic can be made to work with it also.

Tracking this problem here: https://github.com/bogle-tools/site/issues/79

I've taken a stab at importing CSVs from Vanguard Mutual Fund Account (orig account type).
Sample file: https://github.com/bogle-tools/financia ... d-orig.csv
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

I've started to track the status of all file formats in a "table" in the first post of this thread.

Please keep the suggestions and file format examples coming, we are up to 7 different file formats being importable now!
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

From: viewtopic.php?t=398522
albireo13 wrote: Fri Feb 24, 2023 4:00 pm I confess I still have my IRA at RJ.
albireo13 (or others with Raymond James accounts) - please see top post above. If you have access to a CSV file (or other data file download) for you Raymond James portfolio, can you please anonymize the data file, and share that with me? I’d like to help people like you get a good good portfolio review, more easily…
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Hello-
I'm quoting several people who may still have access to a Raymond James online login.

As you can see from this thread, I'm trying to get access to a CSV file that a Raymond James user downloads from RaymondJames.com
I'd like to add support to https://bogle.tools/portfolio to import account info for Raymond James customers.

Can one of you please help?

See the first post in this thread for details...and ask if you have questions.

(sorry for the wide use of quoting here...just trying to find somebody still logging into their account and checking their notifications...)

Thanks, Rob
TCB2020 wrote: Tue May 26, 2020 2:16 pm Hi. I’m in a no fee account, but my advisor doesn’t do a thing. I have just north of 100K amongst bonds, 529, IRA and then the usual 19 fund Raymond James special. Almost all are down.
I will be switching to TD Ameritrade...
Cranberry44 wrote: Sun Jun 28, 2020 9:59 am I want to leave Raymond James (RJ), but I’m having a lot of trouble deciding where to move, and it’s causing me a lot of stress — I need help making this decision. I’d like to have this processes started by then end of July if not much much sooner.
...
*UPDATE*
I transferred to Fidelity. It was scarily easy; I only regret not doing it earlier. I submitted the transfer request on a Friday afternoon and the full process was completed by the next Wednesday. Raymond James charged $250 total to transfer/close my taxable account and Roth IRA, and Fidelity agreed to reimburse the full fee. I have since added a Cash Management Account and was accepted for their 2% cash back card. A great and easy experience overall. Thanks again to all on the forum.
...
mrsvortex wrote: Thu Jun 23, 2022 2:47 pm My husband and I have gotten more financially literate in the last year and are currently in the process of moving our assets from Raymond James to Fidelity to be DIY investors.
Youngbuck21 wrote: Mon Dec 28, 2020 8:39 am Good morning. I keep seeing everyone posting about leaving Raymond James due to the fees.
albireo13 wrote: Fri Feb 24, 2023 4:00 pm I confess I still have my IRA at RJ.
investorstarter wrote: Mon Dec 02, 2019 4:52 pm I'm new to this forum relatively and I have a question in regard to my Solo 401k(tax deferred). I've been with raymond james for year and half and would like to break that relationship ASAP as fees are crazy and high.
flyingsquirrel wrote: Mon Feb 10, 2020 1:54 pm My SO and I have Roth IRAs at Raymond James. I have been wanting to get out of there just have not pulled the trigger because I’m scared and needed to educate my self more.
Apexbbq1 wrote: Sat Mar 02, 2019 12:40 pm Help! I need to divorce raymond james and move to vanguard or similar. The fees are killing me.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Recently added importing for Share count and Cost Basis from all the supported file formats.

Share count - supported in all.
Cost Basis - supported in all, except vanguard - vanguard doesn't include that data in their CSV file formats.

Haven't yet surfaced in UI, but tracking with this issue: use share quantity and cost basis, now that it is imported #83
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Added Ameriprise CSV parsing support. (updated table in first post)

I didn't have a perfect example CSV to test with. Please let me know if somebody tries...let me know either way...success - i'll smile...failure - i'll work with you to help fix it.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Rob Relyea wrote: Wed Feb 22, 2023 6:42 am
placeholder wrote: Tue Feb 21, 2023 11:45 pm There are a few new custodians notably public.com that don't have account download but because they use apex clearing under the hood you can create a corresponding apex account and get a nice csv download of the information so right now mine has the following columns:

Code: Select all

Symbol,Description,Qty,Price,Market Value,Day Change
EFV,ISHARES TRUST ISHARES MSCI EAFE VALUE ETF,xxx,xxx,xxx,xxx
You can select the columns to display and the order of them left to right.
Thanks placeholder for the pointer to public/apex.
Adding that to my backlog with: https://github.com/bogle-tools/site/issues/78

Have never heard of public before, but I see they may be interesting because of Brokerage transfer bonuses.

Closed issue #78 as not planned. (updated table in first post)
not planning on doing this now...
if somebody wants to donate a bit of their "brokerage bonus" to fund this work, happy to reconsider.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

I just programmed and published support for importing T Rowe Price CSV files. If you have a T Rowe Price account and can give it a try, let me know if you see any problems after importing.

See https://bogle.tools/import-accounts for directions.

Can be used for a Portfolio Review via https://bogle.tools/portfolio-review
or for Portfolio Tracker via https://bogle.tools/portfolio

I've updated the list of Custodians (brokers) that these tools can currently import in the first post of this thread.

As always, if there is a Custodian that you'd like to see support added for, please let me know...and help me get a CSV file so I can understand the shape of the data.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

In order to improve the CSV importing, I did a fairly major change to the programming code. My testing shows that everything works the same or better...but let me know if you notice any problems with importing your data from your brokerage CSV files.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Have a friend who will likely give me access to an anonymized Raymond James csv file soon. Excited to be able to add support for that.
[Update 1/5/2024 - still don't have access to a Raymond James csv file...this one fell through]

Anybody have another Custodian CSV that would be nice to support in https://bogle.tools/portfolio ?
Last edited by Rob Relyea on Fri Jan 05, 2024 12:25 pm, edited 2 times in total.
bombcar
Posts: 889
Joined: Sun Aug 12, 2007 6:41 pm

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by bombcar »

As a complete aside, ModernCSV is great for editing CSV files and avoids lots of the Excel problems.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Yes, related but very different. Thanks for the pointer…I may try it out in the future.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Big bug found with Vanguard CSV importer used with bogle.tools/portfolio-tracker (or bogle.tools/portfolio-review)
If you have a 401k plan with Vanguard, your plan data is listed in the CSV download separately from normal brokerage data.

I've added a Plan section to the sample data file published here: https://github.com/bogle-tools/financia ... 31a03f87e2

Will work on fix to harvest the data from that location now...[update: now is fixed...but my sample data had no tickers, and also no account number, so it may get enhanced in future as I get better examples]
techbud
Posts: 140
Joined: Thu Dec 22, 2022 6:52 am

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by techbud »

Two items:

1) I'm having a problem importing data from Schwab. I have two accounts at Schwab; a taxable account and a tIRA. I first imported the tIRA. When I try to import the data for the taxable account, it overwrites the data for the IRA that was previously imported. Is there are way around this via the settings, or do I need to edit something in the CSV so that the importer can distinguish between the two accounts?
(Also, do you prefer bug reports here, or would you prefer me to open a GitHub issue?)

2) Would you like a CSV sample for JP Morgan Invest (Chase)? Here are the headers and I can provide a CSV if it helps.

Code: Select all

Asset Class	Asset Strategy	Asset Strategy Detail	Description	Ticker	CUSIP	Quantity	Base CCY	Local CCY	Price	PriceInd	Local Price	Today's Price Change	Price Change %	Pricing Date	Value	Today's Value Change	Value Change %	Local Value	Cost	Unit Cost	Local Unit Cost	Orig Cost (Base)	Orig Cost (Local)	Cost Source	Local Cost	Unrealized G/L Amt.	Orig. $ Gain/Loss (Base)	Orig. $ Gain/Loss (Local)	Local Unrealized G/L Amt.	Unrealized Gain/Loss (%)	Orig. % Gain/Loss (Base)	Orig. % Gain/Loss (Local)	Local Unrealized Gain/Loss (%)	Disallowed Loss (Base)	Disallowed Loss (Local)	Acquisition Date	Adj Date	Accrued/Income Earned	Local Accrued/Income Earned	Accrued Income	Local Accrued Income	Est. Annual Income	Local Est. Annual Income	YTM	Maturity Date	Coupon Rate	S&P Rating	Moody Rating	Buy/Call Amount	Buy/Call Currency	Sell/Put Amount	Sell/Put Currency	Market Spot Rate	Market Forward Rate	Contract Rate	Subscription Amount	Net Distributions	Used/Outstanding	Interest Rate	Finance Charges (MTD)	As of	Acct Type	Accounting Method	Current Face Value	Disclaimers-Cost	Disclaimers-Quantity 

User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

techbud wrote: Fri Jan 05, 2024 3:28 pm (Also, do you prefer bug reports here, or would you prefer me to open a GitHub issue?)
either is fine...here is likely nicer because the community can see it...(not many of these folks are on github)

techbud wrote: Fri Jan 05, 2024 3:28 pm Two items:
1) I'm having a problem importing data from Schwab. I have two accounts at Schwab; a taxable account and a tIRA. I first imported the tIRA. When I try to import the data for the taxable account, it overwrites the data for the IRA that was previously imported. Is there are way around this via the settings, or do I need to edit something in the CSV so that the importer can distinguish between the two accounts?
I made a small fix to Schwab parsing that might help this.
For other Custodians, it usually puts *#### (the last four digits of an account number in an account.Note property. And it uses that Note value to compare new accounts to old accounts.

For Schwab, before the change i just checked in, it trimmed the first character and the last character...so Individual ...321 would be stored as "ndividual ...32" -- so if two account numbers were 321 and 325 or 327 ... they would appear to be a match.
My fix will leave all characters from that string...and all numbers.

Can you try with the updated site? If it still has problems, it would help if I could know the "account name/number suffix". For example, line 3 and line 11 of my sample Schwab file from here: https://github.com/bogle-tools/financia ... schwab.csv
Line3: "Individual ...321","","","","","","","","","","","","","","","",""
Line11: "Individual ...432","","","","","","","","","","","","","","","",""

techbud wrote: Fri Jan 05, 2024 3:28 pm 2) Would you like a CSV sample for JP Morgan Invest (Chase)? Here are the headers and I can provide a CSV if it helps.

Code: Select all

Asset Class	Asset Strategy	Asset Strategy Detail	Description	Ticker	CUSIP	Quantity	Base CCY	Local CCY	Price	PriceInd	Local Price	Today's Price Change	Price Change %	Pricing Date	Value	Today's Value Change	Value Change %	Local Value	Cost	Unit Cost	Local Unit Cost	Orig Cost (Base)	Orig Cost (Local)	Cost Source	Local Cost	Unrealized G/L Amt.	Orig. $ Gain/Loss (Base)	Orig. $ Gain/Loss (Local)	Local Unrealized G/L Amt.	Unrealized Gain/Loss (%)	Orig. % Gain/Loss (Base)	Orig. % Gain/Loss (Local)	Local Unrealized Gain/Loss (%)	Disallowed Loss (Base)	Disallowed Loss (Local)	Acquisition Date	Adj Date	Accrued/Income Earned	Local Accrued/Income Earned	Accrued Income	Local Accrued Income	Est. Annual Income	Local Est. Annual Income	YTM	Maturity Date	Coupon Rate	S&P Rating	Moody Rating	Buy/Call Amount	Buy/Call Currency	Sell/Put Amount	Sell/Put Currency	Market Spot Rate	Market Forward Rate	Contract Rate	Subscription Amount	Net Distributions	Used/Outstanding	Interest Rate	Finance Charges (MTD)	As of	Acct Type	Accounting Method	Current Face Value	Disclaimers-Cost	Disclaimers-Quantity 
Would love to get that CSV...so I can add support for it!! Thanks.

Would be much better to have an edited version of the full CSV. Just the header will be difficult for me to correctly understand and parse the data rows.
Perhaps you could send me a file of JP Morgan Invest (Chase) that would remove any personal identifying or account numbers .... and change investment quantity and which investments you have, as you desiire.
Ideally, you'd be able to obfuscate it well enough for me to publicly store it here: https://github.com/bogle-tools/financia ... custodians (with no privacy or similar loss)

Emailing it suggestions@bogle.tools would work well. But you could private message it to me, or ....

Thanks, Rob
techbud
Posts: 140
Joined: Thu Dec 22, 2022 6:52 am

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by techbud »

Rob Relyea wrote: Fri Jan 05, 2024 6:40 pm I made a small fix to Schwab parsing that might help this.
Rob Relyea wrote: Fri Jan 05, 2024 6:40 pm Would love to get that CSV...so I can add support for it!! Thanks.
I'll send you a PM with the a sample file. Doesn't seem like it has any PII in it so I'm ok sharing.

BTW, any chance you could you add the link to "https://bogle.tools/import-accounts" to the sidebar menu? I find I have to dig around to find it.

Thanks!
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

techbud wrote: Sat Jan 06, 2024 8:29 am I'll send you a PM with the a sample file. Doesn't seem like it has any PII in it so I'm ok sharing.

BTW, any chance you could you add the link to "https://bogle.tools/import-accounts" to the sidebar menu? I find I have to dig around to find it.

Thanks!
So, I assume the Schwab importing fix worked for you?

Just added an Import CSV(s) link on sidebar menu. Thanks for the suggestion.

Thanks for the JP Morgan Chase sample csv file. Will report back when I’ve added support for importing those.

Keep any suggestions/requests coming.
Thanks!
-Rob
techbud
Posts: 140
Joined: Thu Dec 22, 2022 6:52 am

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by techbud »

Rob Relyea wrote: Sat Jan 06, 2024 10:46 am
So, I assume the Schwab importing fix worked for you?
Oops sorry, yes it worked; I forgot to add it in the prior post. thanks for the quick fix!
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

techbud wrote: Sat Jan 06, 2024 11:05 am Oops sorry, yes it worked; I forgot to add it in the prior post. thanks for the quick fix!
Great to hear. I assumed, but wanted to confirm.


Just added support for JPMorgan Chase CSV files, thanks to you sharing an example CSV file with me!
Please let me know if you see any problems or improvements.
The one strange thing about JPMorgan-Chase.csv files is that they don't have any Account Name or Account Number.
So that means if you have two different CSV files that you download from them, bogle.tools will think that the first import account is being updated by the second CSV. Workaround for now would be to add a Note to the account after importing the first one. And then importing the second one.

Thanks again,
Rob
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

When you click "Import CSV File(s)", it navigates you to http://bogle.tools/import-accounts

We've added detailed instructions for Schwab CSV downloading.
Detailed steps for Schwab accounts:

Log into schwab.com↗️
Click on the Positions↗️ link
Click on the Export link, found on the top-right of page.
Read schwab warnings to "exercise caution when downloading data". If ok after reading, press ok to continue.
A file named [Account Type]-Positions-[DATE]-######.csv will be downloaded via your browser
Click "Choose File" button on this page, find and choose the [Account Type]-Positions-[DATE]-######.csv file in Downloads folder
Choose Account Type for each new account
That page still has general instructions steps for many brokerages:
Basic steps for Ameriprise, eTrade, JPMorgan Chase, Merrill Edge, Morgan Stanley, or T Rowe Price accounts
If you have access to one of those accounts, and wanted to write up detailed steps on how to download a CSV for those accounts, we would love to add more detailed help for those users.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools/portfolio - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Rob Relyea wrote: Mon Feb 20, 2023 12:24 pm Would love to have your help to get:
  • Edward Jones CSV File
  • Raymond Jones CSV File
...and others
Anybody with access to Edward Jones or Raymond James account able to help others?

This will help people who are discovering bogleheads investment philosophy and our community's Portfolio Reviews.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

We've improved the design of the import center. It now only shows instructions for one custodian at a time. Each Custodian also has a URL which can navigate you to those directions.

Please let us know if you have any suggestions to improve it more.

Image
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Added support for Treasury Direct import via copy/paste. See discussion in this other thread: viewtopic.php?p=7682682#p7682682
techbud
Posts: 140
Joined: Thu Dec 22, 2022 6:52 am

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by techbud »

I'm having trouble again importing my Schwab accounts. Wonder if a regression crept into the code or if Schwab changed their export format.
Here's what I'm doing, starting with an empty (new) profile on bogle.tools:

1. From schwab.com, I am exporting my IRA account as a CSV. The first line of the csv file is:

Code: Select all

Positions for account Techbud IRA ...567 as of 12:06 PM ET, 2024/01/31
2. I import this file successfully into bogle.tools.
3. Back on schwab.com, I switch to my joint taxable account and export it as a CSV. The first line of the CSV file is:

Code: Select all

Positions for account Techbud Joint ...234 as of 12:10 PM ET, 2024/01/31
4. I then try to import this into bogle.tools, but it ends up replacing my IRA account rather than creating a new account.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Are you following the same instructions as bogle.tools/import/Schwab which clicks the export link after going to Positions link?

Because when one does that, the CSV format is:

Code: Select all

"Positions for CUSTACCS as of ##:## PM ET, 01/31/2024","","","","","","","","","","","","","","","",""
"","","","","","","","","","","","","","","","",""
"Joint_Tenant ...123","","","","","","","","","","","","","","","",""
"Symbol","Description","Quantity","Price","Price Change %","Price Change $","Market Value","Day Change %","Day Change $","Cost Basis","Gain/Loss %","Gain/Loss $","Ratings","Reinvest Dividends?","Capital Gains?","% Of Account","Security Type"
...
It looks like the way you are downloading is a single account CSV file...because your account description and end of the account number is on the first line.

Please let me know the exact steps you follow to create your CSV, so I can duplicate those steps...and see what I get when I try your steps.

When I test imports with schwab.csv, then schwab2.csv (both downloaded ahead of time from https://github.com/bogle-tools/financia ... custodians) (same data, but account numbers reversed) -- it doesn't have the same problem you describe...i end up with 4 accounts, as expected.

Note: i just fixed the schwab importer (3 minutes ago) to only grab the last three numbers of the account...for the Note field for the imported account. Just makes it similar to all the other importers...shouldn't impact this problem one way or another.
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

techbud wrote: Wed Jan 31, 2024 11:22 am I'm having trouble again importing my Schwab accounts. Wonder if a regression crept into the code or if Schwab changed their export format.
actually, i think i figured out how you downloaded your CSVs.

In the positions page, there is a drop down list box.
My CSV file format happens when you choose "All Brokerage Accounts".
Your CSV file format happens when you choose a specific account.

Let me go figure out how to handle both cases.
Tracking this issue: support schwab CSV file that holds only 1 account #291

Thanks for finding this hiccup!
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Rob Relyea wrote: Wed Jan 31, 2024 3:53 pm
techbud wrote: Wed Jan 31, 2024 11:22 am I'm having trouble again importing my Schwab accounts. Wonder if a regression crept into the code or if Schwab changed their export format.
actually, i think i figured out how you downloaded your CSVs.

In the positions page, there is a drop down list box.
My CSV file format happens when you choose "All Brokerage Accounts".
Your CSV file format happens when you choose a specific account.

Let me go figure out how to handle both cases.
Tracking this issue: support schwab CSV file that holds only 1 account #291

Thanks for finding this hiccup!
Should be fixed. I believe the portfolio tracker will now properly support schwab single account CSV format or schwab multiple account CSV format.

Thanks again for finding this new format I hadn't learned about!
techbud
Posts: 140
Joined: Thu Dec 22, 2022 6:52 am

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by techbud »

Rob Relyea wrote: Wed Jan 31, 2024 4:50 pm
Rob Relyea wrote: Wed Jan 31, 2024 3:53 pm
techbud wrote: Wed Jan 31, 2024 11:22 am I'm having trouble again importing my Schwab accounts. Wonder if a regression crept into the code or if Schwab changed their export format.
actually, i think i figured out how you downloaded your CSVs.

In the positions page, there is a drop down list box.
My CSV file format happens when you choose "All Brokerage Accounts".
Your CSV file format happens when you choose a specific account.

Let me go figure out how to handle both cases.
Tracking this issue: support schwab CSV file that holds only 1 account #291

Thanks for finding this hiccup!
Should be fixed. I believe the portfolio tracker will now properly support schwab single account CSV format or schwab multiple account CSV format.

Thanks again for finding this new format I hadn't learned about!
Fix seems to have worked. thanks!
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Redbard wrote: Sun Feb 11, 2024 7:14 am I attempted to add my portfolio to your tool but no joy.
It would not accept OfxDownload.cvs I downloaded from Vanguard (1 yr and 18mo)
Also curious, when you select File to import, to tool lists media vs normal file selection options
Sorry that you are running into problems.
I've replied here on the thread that is dedicated to CSV file importing.

I keep examples of all the CSV files that my tools can import here: https://github.com/bogle-tools/financia ... custodians
there is a vanguard.csv and a vanguard-orig.csv

I'd like to understand what about your file is causing my software problems...so that I can fix it.
In order to do that, here are a few things you could do:
1) make a copy of that file

2) anonymize any information in the file that you'd like to keep private from me
a) often changing an account number from 3457122 to a number of the same length and type of characters (digits in this case) helps keep your info private, but helps me understand the "makeup" of the file: new number: "1234567".

b) for some or all of the rows, changing the number of shares, and total $ amount - while maintaining the type of characters (you don't need to make the total $ amount be correct...just make it a valid number)
1,437 -> 1,234
1437 -> 1234
$1,542.12 -> $1,234.56

c) you can remove some rows, but try to keep the overall shape of the file... if it has two sections, keep two sections with data rows in both of them.

3) verify that your modified file still fails to import (test it again...does it seem to fail in the same way)

4) send anonymized file to me
a) send file contents (copy and paste into a private message) to "Rob Relyea" here on bogleheads
b) or email file to suggestions@bogle.tools
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Redbard wrote: Sun Feb 11, 2024 7:14 am Also curious, when you select File to import, to tool lists media vs normal file selection options
Unsure exactly what this sentence means. I think your laptop, desktop, or phone, may be presenting you with options on where to find the file to upload. The answer is to find the same file you downloaded from vanguard...which is sometimes tricky.
User avatar
Redbard
Posts: 44
Joined: Sun Jan 14, 2024 1:15 pm

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Redbard »

Rob Relyea wrote: Sun Feb 11, 2024 8:57 am
Redbard wrote: Sun Feb 11, 2024 7:14 am Also curious, when you select File to import, to tool lists media vs normal file selection options
Unsure exactly what this sentence means. I think your laptop, desktop, or phone, may be presenting you with options on where to find the file to upload. The answer is to find the same file you downloaded from vanguard...which is sometimes tricky.
Um? It wasn't the where by what. Since your tool is expecting to find a csv file having the selections to choose from being "media" seems unusual although you can drill down to a csv file just fine form there.
Anyway I'll sort out the import just fine from your other instructions.
Thanks
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Hoping to add support for wells fargo file format soon. Had some emails/private messages with somebody who could benefit from bogle.tools importing wells fargo data (downloaded as spreadsheet). Hope to get that spreadsheet (modified as necessary to protect privacy, as needed) so I can build support soon!

Thanks, Rob
User avatar
Topic Author
Rob Relyea
Posts: 418
Joined: Tue Dec 20, 2016 11:29 pm
Location: Bellevue, WA
Contact:

Re: bogle.tools - help others with more CSV File Format examples from different Custodians

Post by Rob Relyea »

Redbard wrote: Sun Feb 11, 2024 9:13 am
Rob Relyea wrote: Sun Feb 11, 2024 8:57 am
Redbard wrote: Sun Feb 11, 2024 7:14 am Also curious, when you select File to import, to tool lists media vs normal file selection options
Unsure exactly what this sentence means. I think your laptop, desktop, or phone, may be presenting you with options on where to find the file to upload. The answer is to find the same file you downloaded from vanguard...which is sometimes tricky.
Um? It wasn't the where by what. Since your tool is expecting to find a csv file having the selections to choose from being "media" seems unusual although you can drill down to a csv file just fine form there.
Anyway I'll sort out the import just fine from your other instructions.
Thanks
What platform (iOS, Android, Mac, Windows, etc...) and which browser (Chrome, Edge, Firefox, etc...) are you using?
That way I'll be able to try it out where you are...and understand "media" from your comments.
Post Reply