A CSV converter for Charles Schwab

rlan · October 26, 2022, 12:51pm

Hello all,

I have written a Python program that converts a Charles Schwab transaction CSV file to a ready-to-import CSV file for Portfolio Performance.

Also included is a complete step-by-step guide (with screenshots) for creating a new portfolio file in PP and importing a converted example CSV. As far as I can test, PP will detect and skip duplicate transactions. So it is safe to import overlapping transactions in the future.

If you try it, please share your feedbacks!

On GitHub: https://github.com/rlan/convert-csv-schwab2pp

Regards,

Nirus · October 26, 2022, 3:45pm

Hallo @rlan
I think you should add the currencies of the amounts.

Alex

rlan · October 26, 2022, 4:12pm

Hello Alex,
Good idea, thanks! Will try.

bobo2bobo · November 13, 2022, 2:39pm

Hey @rlan,
so glad that you are working on this!
I am a complete python noob so maybe I did something wrong but I end up with these errors…

File "/Users/xxx/Documents/PPSchwabConverter/convert.py", line 33, in <module>
    df['Date'] = pd.to_datetime(df['Date'], format='%m/%d/%Y')                 
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/core/tools/datetimes.py", line 1064, in to_datetime
    cache_array = _maybe_cache(arg, format, cache, convert_listlike)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/core/tools/datetimes.py", line 229, in _maybe_cache
    cache_dates = convert_listlike(unique_dates, format)
                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/core/tools/datetimes.py", line 430, in _convert_listlike_datetimes
    res = _to_datetime_with_format(
          ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/core/tools/datetimes.py", line 538, in _to_datetime_with_format
    res = _array_strptime_with_fallback(
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Library/Frameworks/Python.framework/Versions/3.11/lib/python3.11/site-packages/pandas/core/tools/datetimes.py", line 473, in _array_strptime_with_fallback
    result, timezones = array_strptime(arg, fmt, exact=exact, errors=errors)
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "pandas/_libs/tslibs/strptime.pyx", line 156, in pandas._libs.tslibs.strptime.array_strptime
ValueError: unconverted data remains:  as of 08/24/2022
xxxxx:PPSchwabConverter xxx$

rlan · November 13, 2022, 11:31pm

Hello @bobo2bobo,

Thank you for trying my tool.

My converter also depends on local python runtime setup. To help me debug your issue, would you please try these:

Run the converter on the example. Please share any errors and the output file.

python3 convert.py example.csv -p example_out.csv

I suspect the date format in your csv file is different from mine and caused the errors. Please share the first few lines of yours. To anonymize your data, I suggest editing (but not deleting) these:

A. Account number on the first line.
B. Data in the Description column
C. Data in the Amount column

First 3 lines from the example are:

"Transactions  for account ...000 as of 09/27/2022 02:03:53 AM ET"
"Date","Action","Symbol","Description","Quantity","Price","Fees & Comm","Amount"
"12/29/2021","NRA Withholding","BNDX","VANGUARD TOTAL INTERNATIONAL BND ETF","","","","-$0.14"

Thanks!

flywire · November 14, 2022, 2:52am

Can we align this with Import CSV file ?

rlan · November 14, 2022, 3:51am

@flywire Good question. I did try that method before trying to write my own program. Here was my logic: The tech debt of understanding and verifying to my needs is probably same or higher if I write a program from scratch.

My needs:

I want a way to load a CSV with (as much as possible) default settings in the PP CSV importer.
Mapping multiple Types to one, e.g. “NRA Tax Adj” and “NRA Withholding” to Taxes.
Schwab CSV has all transactions of the account: cash (dividends), taxes and buy/sell.
No duplicates when overlapping data is given.
Future tech debt. Create variations of my program to support other financial institutions that I use.

You are welcome to use the example in my repo and the resulting PP records (see guide).

Cheers

bobo2bobo · November 15, 2022, 9:18am

Thanks for the reply!

Running on example.csv works flawless - no errors

Date,Note,Ticker Symbol,Security Name,Shares,Fees,Value,Transaction Currency,Type
20211229,NRA Withholding,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,-0.14,USD,Taxes
20211229,Short Term Cap Gain,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,0.48,USD,Dividend
20211229,Long Term Cap Gain,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,29.21,USD,Dividend
20210304,NRA Tax Adj,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,-1.10,USD,Taxes
20210304,Cash Dividend,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,3.65,USD,Dividend
20210225,NRA Tax Adj SCHWAB1 INT 01/28-02/24,,,,,-0.02,USD,Taxes
20210225,Credit Interest SCHWAB1 INT 01/28-02/24,,,,,0.09,USD,Interest
20210209,Buy,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,81.0,,-4697.99,USD,Buy
20210202,Wire Received,,,,,5000.00,USD,Deposit

These are the first three lines of my schwab.csv

Transactions  for account ...xxx as of 11/13/2022 08:53:37 AM ET
Date,"Action","Symbol","Description","Quantity","Price","Fees & Comm","Amount"
10/31/2022,"NRA Tax Adj","EOS","EATON VANCE ENHANCED EQT","","","","-$20.60"

Does that help?

Dominik

rlan · November 15, 2022, 12:05pm

Your converted example is different from mine:

Date,Note,Ticker Symbol,Security Name,Shares,Fees,Value,Transaction Currency,Type
20211229,NRA Withholding,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,-0.14,USD,Taxes

Between ETF and -0.14, there are 3 comma’s (2nd line above), but your has 1. A CSV file is a pure text file. Just to check, did you copy and paste from a text editor? ie not from Excel or something that could alter the raw content.

I added a suffix line to your schwab.csv:

Transactions for account ...xxx as of 11/13/2022 08:53:37 AM ET
Date,"Action","Symbol","Description","Quantity","Price","Fees & Comm","Amount"
10/31/2022,"NRA Tax Adj","EOS","EATON VANCE ENHANCED EQT","","","","-$20.60"
"Transactions Total","","","","","","","$94.33",

This suffix is part of a downloaded Schwab CSV file. I just copied the one from my example.

Conversion output (no errors):

Date,Note,Ticker Symbol,Security Name,Shares,Fees,Value,Transaction Currency,Type
20221031,NRA Tax Adj,EOS,EATON VANCE ENHANCED EQT,,,-20.60,USD,Taxes

It imports correctly in PP.

Reading your original error logs, I think you are running macOS Ventura and used the built-in Python 3.11. I’m still on Monterey. For the above test, I am using python 3.11 in a python virutalenv. This is as close as I can get to replicate your environment, but still not identical.

Could you try the following? It prints Pandas’ version. I’m running 1.5.1.

python3 -c 'import pandas; print(pandas.__version__)'

bobo2bobo · November 15, 2022, 2:47pm

You are right. As pure text file it looks like this:

Date,Note,Ticker Symbol,Security Name,Shares,Fees,Value,Transaction Currency,Type
20211229,NRA Withholding,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,-0.14,USD,Taxes
20211229,Short Term Cap Gain,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,0.48,USD,Dividend
20211229,Long Term Cap Gain,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,29.21,USD,Dividend
20210304,NRA Tax Adj,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,-1.10,USD,Taxes
20210304,Cash Dividend,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,,,3.65,USD,Dividend
20210225,NRA Tax Adj SCHWAB1 INT 01/28-02/24,,,,,-0.02,USD,Taxes
20210225,Credit Interest SCHWAB1 INT 01/28-02/24,,,,,0.09,USD,Interest
20210209,Buy,BNDX,VANGUARD TOTAL INTERNATIONAL BND ETF,81.0,,-4697.99,USD,Buy
20210202,Wire Received,,,,,5000.00,USD,Deposit

Panda 1.5.1. for me, too on Monterey 12.6

rlan · November 16, 2022, 1:40am

Thank you @bobo2bobo for your patience in debugging with me.

Result of (3) is a unexpected to me. I would like to ask for more patience.

Instead of finding the root cause between your environment and mine, I would like to suggest a 3rd party environment that is both time-invariant and repeatable.

The link below will launch a Google Colab session. It runs python code in the cloud. Please follow the instructions there. Although free, one caveat is one needs a Google account to use it.

In case that you prefer a 100% offline way, I am adding instructions to replicate my python virtual environment to Github. Unfortunately, this method is more technically involved.

Let me know if there are more questions.

rlan · November 16, 2022, 7:55am

Instructions added to my Github for both methods: Colab and virtual environment.

bobo2bobo · November 17, 2022, 8:54am

it does not create the pp file …

Traceback (most recent call last):

 File "convert.py", line 37, in <module>
    engine='python')
  File "/usr/local/lib/python3.7/dist-packages/pandas/util/_decorators.py", line 311, in wrapper
    return func(*args, **kwargs)
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/parsers/readers.py", line 586, in read_csv
    return _read(filepath_or_buffer, kwds)
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/parsers/readers.py", line 482, in _read
    parser = TextFileReader(filepath_or_buffer, **kwds)
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/parsers/readers.py", line 811, in __init__
    self._engine = self._make_engine(self.engine)
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/parsers/readers.py", line 1040, in _make_engine
    return mapping[engine](self.f, **self.options)  # type: ignore[call-arg]
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/parsers/python_parser.py", line 113, in __init__
    ) = self._infer_columns()
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/parsers/python_parser.py", line 376, in _infer_columns
    line = self._buffered_line()
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/parsers/python_parser.py", line 584, in _buffered_line
    return self._next_line()
  File "/usr/local/lib/python3.7/dist-packages/pandas/io/parsers/python_parser.py", line 678, in _next_line
    next(self.data)
  File "/usr/lib/python3.7/codecs.py", line 322, in decode
    (result, consumed) = self._buffer_decode(data, self.errors, final)
UnicodeDecodeError: 'utf-8' codec can't decode byte 0xc9 in position 26: invalid continuation byte

rlan · November 18, 2022, 12:12am

This looks like text encoding issue. I’m using Schwab in English. Is your Schwab account not in English? If so what language?

rlan · November 18, 2022, 1:40am

@bobo2bobo Could you also run below in your macOS terminal? schwab.csv is your CSV.

file -I schwab.csv

My result is

schwab.csv: text/plain; charset=us-ascii

bobo2bobo · January 9, 2023, 12:25pm

Happy new Year! It is in English!