instructions on how to download gains and losses sheet from etrade and then estimate capital gains tax

est-tax-etrade-GL-collapsed.md

Quick Way to Estimate Taxes Owed on Capital Gains (E*Trade)

This short explainer goes through

1. where to download your Gains and Losses sheet on one E*Trade account.
2. how to quickly estimate taxes owned.

---

(1) Downloading Gains & Losses Sheet (G&L_collapsed.xlsx) from E*Trade

• Go to Stock Plan > My Account > Gains & Losses.
• Select the proper tax year (e.g. 2022) then find the Download button towards the right side of the webpage below the Apply button.
• Click the Download button and select "Download Collapsed".
  • This will download the appropriate excel sheet: G&L_Collapsed.xlsx.

(2) Estimate Taxes using awk

Pre-requisites

Install xlsx2csv

pip3 install xlsx2csv

After installing xlsx2csv, its possible to estimate the long- and short-term capital gains tax owed from an etrade collapsed gains and losses (G&L) xlsx export.

xlsx2csv G\&L_Collapsed.xlsx --all | tail -n +4 | 
	awk -F "," -v OFS=", " '{a[$2 OFS $18]+=$16 } END {for (i in a) print i,a[i], (i=="AAPL, Short Term" ? 0.30*a[i] : .15*a[i])}' | 
	awk -F, '{s+= $4} END {print s}'

First, let's refresh ourselves on the different rates for capital gains (short- vs long-term).

Tax Rates on Gains

SOURCE: https://www.nerdwallet.com/article/taxes/capital-gains-tax-rates

• "The short-term capital gains tax rate equals your ordinary income tax rate — your tax bracket."
• "The long-term capital gains tax rate is 0%, 15% or 20%, depending on your taxable income and filing status."
• "Some investors may owe an additional 3.8% that applies to whichever is smaller: Your net investment income or the amount by which your modified adjusted gross income exceeds the amounts listed below."

Let's assume the following short-term and long-term capital gains tax rates.

type	rate
short-term	.30
long-term	.15

Breaking down the code

Let's take the main parts of the code, bit by bit.

• xlsx2csv G\&L_Collapsed.xlsx --all | tail -n +4 |
  • Convert the xlsx sheet to csv then skip the first 3 lines (-n +4 means start on the 4th line)
• awk -F "," -v OFS=", " '{a[$2 OFS $18]+=$16 } END {for (i in a) print i,a[i], (i=="AAPL, Short Term" ? 0.30*a[i] : .15*a[i])}' |
  • Comma is both the input delimiter (-F ",") an output delimiter (-v OFS=", ")
  • Create an array a indexed by fields 2 (Symbol) and 18 (Term), separated by OFS (a[$2 OFS $18]) e.g. a[AAPL, Short Term]
  • Per array value, sum up (a[$2 OFS $18]+=$16) over field 16 (Adjusted Gain/Loss)
  • Iterate over array a indexes i and the print the indexes (e.g. AAPL, Short Term), array values (sums over gains or losses), and estimated tax (i, a[i], (i=="AAPL, Short Term" ? 0.30*a[i] : .15*a[i]) conditional on whether its short or long term.
• awk -F, '{s+=$4} END {print s}'
  • Lastly, we sum up over the tax estimates in field 4, assign the value to s and print.