ETrade Capital Gains Calculator (Includes Quarterly Estimated Taxes)

calculate-capital-gains.go

// Thanks to @dantonnoriega for getting me started in the right direction:
// https://gist.github.com/dantonnoriega/fa0d3cdbb0b4012f217262c1bf405132
//
// To get your G&L_Collapsed.xlsx (quoting Dan's gist):
// Go to ETrade > 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.
//
// Usage:
//   go run calculate-capital-gains.go path/to/G&L_Collapsed.xlsx
//
// Output looks like:
//
// Capital Gains Tax Estimate Breakdown:
// --------------------------------------------------
// Total Reported Gain (from Summary): $85,000.00
//
// ENE Short Term:
//   Total Gain: $45,000.00 | Tax Rate: 30.0% | Estimated Tax: $13,500.00
//
// ENE Long Term:
//   Total Gain: $40,000.00 | Tax Rate: 15.0% | Estimated Tax: $6,000.00
//
// Quarterly Tax Breakdown:
// --------------------------------------------------
//
// Q1: Total Gains: $20,000.00 | Estimated Tax: $4,500.00
//   Transactions:
//     ENE (Short Term) - Date: 2023-01-15 | Gain: $8,000.00 | Tax: $2,400.00
//     ENE (Long Term) - Date: 2023-02-01 | Gain: $12,000.00 | Tax: $1,800.00
//
// Q2: Total Gains: $25,000.00 | Estimated Tax: $5,250.00
//   Transactions:
//     ENE (Short Term) - Date: 2023-04-10 | Gain: $15,000.00 | Tax: $4,500.00
//     ENE (Long Term) - Date: 2023-05-20 | Gain: $10,000.00 | Tax: $1,500.00
//
// Q3: Total Gains: $22,000.00 | Estimated Tax: $4,800.00
//   Transactions:
//     ENE (Short Term) - Date: 2023-07-05 | Gain: $12,000.00 | Tax: $3,600.00
//     ENE (Long Term) - Date: 2023-08-15 | Gain: $10,000.00 | Tax: $1,500.00
//
// Q4: Total Gains: $18,000.00 | Estimated Tax: $4,200.00
//   Transactions:
//     ENE (Short Term) - Date: 2023-10-20 | Gain: $10,000.00 | Tax: $3,000.00
//     ENE (Long Term) - Date: 2023-11-30 | Gain: $8,000.00 | Tax: $1,200.00
//
// --------------------------------------------------
// Total Estimated Tax: $19,500.00
//
// Estimated Quarterly Tax Payments:
// --------------------------------------------------
// Q1 (Typically due April 15): $4,500.00
// Q2 (Typically due June 15): $5,250.00
// Q3 (Typically due September 15): $4,800.00
// Q4 (Typically due January 15): $4,200.00
//
// Note: This is an estimate only. Please consult a tax professional for accurate tax advice.

package main

import (
	"flag"
	"fmt"
	"log"
	"os"
	"strings"
	"time"

	"github.com/araddon/dateparse"
	"github.com/dustin/go-humanize"
	"github.com/shopspring/decimal"
	"github.com/xuri/excelize/v2"
)

// Transaction holds data about a single stock sale transaction.
type Transaction struct {
	Symbol string
	Term   string
	Gain   decimal.Decimal
	Tax    decimal.Decimal
	Date   time.Time
}

// QuarterInfo aggregates all transactions for a given quarter along with totals.
type QuarterInfo struct {
	TotalGain    decimal.Decimal
	TotalTax     decimal.Decimal
	Transactions []Transaction
}

// BreakdownInfo holds summary data for a specific symbol & term combination.
type BreakdownInfo struct {
	TotalGain    decimal.Decimal
	TaxRate      decimal.Decimal
	EstimatedTax decimal.Decimal
}

// parseMoney cleans and converts a string to a decimal.Decimal.
func parseMoney(value string) (decimal.Decimal, error) {
	cleaned := strings.ReplaceAll(value, ",", "")
	cleaned = strings.ReplaceAll(cleaned, "$", "")
	return decimal.NewFromString(strings.TrimSpace(cleaned))
}

// parseDate attempts to parse a date string using github.com/araddon/dateparse.
func parseDate(value string) (time.Time, error) {
	return dateparse.ParseAny(strings.TrimSpace(value))
}

// getTaxQuarter returns which quarter a given date falls into (Q1-Q4).
func getTaxQuarter(date time.Time) string {
	month := int(date.Month())
	return fmt.Sprintf("Q%d", ((month-1)/3)+1)
}

// initializeQuarterlyBreakdown sets up a map for the four quarters.
func initializeQuarterlyBreakdown() map[string]QuarterInfo {
	return map[string]QuarterInfo{
		"Q1": {TotalGain: decimal.Zero, TotalTax: decimal.Zero, Transactions: []Transaction{}},
		"Q2": {TotalGain: decimal.Zero, TotalTax: decimal.Zero, Transactions: []Transaction{}},
		"Q3": {TotalGain: decimal.Zero, TotalTax: decimal.Zero, Transactions: []Transaction{}},
		"Q4": {TotalGain: decimal.Zero, TotalTax: decimal.Zero, Transactions: []Transaction{}},
	}
}

// calculateCapitalGainsTax reads an Excel file, parses transactions, and calculates taxes.
func calculateCapitalGainsTax(filePath string) (decimal.Decimal, map[string]BreakdownInfo, map[string]QuarterInfo, error) {
	const (
		shortTermRate = 0.30
		longTermRate  = 0.15
	)

	taxRates := map[string]decimal.Decimal{
		"Short Term": decimal.NewFromFloat(shortTermRate),
		"Long Term":  decimal.NewFromFloat(longTermRate),
	}

	f, err := excelize.OpenFile(filePath)
	if err != nil {
		return decimal.Zero, nil, nil, fmt.Errorf("failed to open file: %w", err)
	}
	defer func() {
		_ = f.Close()
	}()

	sheets := f.GetSheetList()
	if len(sheets) == 0 {
		return decimal.Zero, nil, nil, fmt.Errorf("no sheets found in Excel file")
	}

	rows, err := f.GetRows(sheets[0])
	if err != nil {
		return decimal.Zero, nil, nil, fmt.Errorf("failed to get rows: %w", err)
	}
	if len(rows) < 1 {
		return decimal.Zero, nil, nil, fmt.Errorf("sheet appears to be empty")
	}

	// Map header names to column indices.
	header := rows[0]
	colIndex := make(map[string]int)
	for i, col := range header {
		colIndex[strings.TrimSpace(col)] = i
	}

	requiredColumns := []string{"Record Type", "Symbol", "Capital Gains Status", "Adjusted Gain/Loss", "Date Sold"}
	for _, col := range requiredColumns {
		if _, ok := colIndex[col]; !ok {
			return decimal.Zero, nil, nil, fmt.Errorf("missing required column: %s", col)
		}
	}

	quarterlyBreakdown := initializeQuarterlyBreakdown()
	taxBreakdown := make(map[string]BreakdownInfo)
	totalTax := decimal.Zero

	for _, row := range rows[1:] {
		// Ensure we have at least enough columns present to read the record type
		if len(row) <= colIndex["Record Type"] {
			continue
		}
		recordType := strings.TrimSpace(row[colIndex["Record Type"]])

		switch recordType {
		case "Sell":
			// Ensure we have enough columns to read needed fields
			if len(row) <= colIndex["Symbol"] ||
				len(row) <= colIndex["Capital Gains Status"] ||
				len(row) <= colIndex["Adjusted Gain/Loss"] ||
				len(row) <= colIndex["Date Sold"] {
				continue
			}

			symbol := strings.TrimSpace(row[colIndex["Symbol"]])
			term := strings.TrimSpace(row[colIndex["Capital Gains Status"]])
			gainVal, err := parseMoney(row[colIndex["Adjusted Gain/Loss"]])
			if err != nil {
				continue
			}
			dateVal, err := parseDate(row[colIndex["Date Sold"]])
			if err != nil {
				continue
			}

			rate, ok := taxRates[term]
			if !ok {
				// If we don't recognize the term, skip
				continue
			}
			taxAmount := gainVal.Mul(rate)
			quarter := getTaxQuarter(dateVal)

			// Update quarterly breakdown.
			qb := quarterlyBreakdown[quarter]
			qb.TotalGain = qb.TotalGain.Add(gainVal)
			qb.TotalTax = qb.TotalTax.Add(taxAmount)
			qb.Transactions = append(qb.Transactions, Transaction{
				Symbol: symbol,
				Term:   term,
				Gain:   gainVal,
				Tax:    taxAmount,
				Date:   dateVal,
			})
			quarterlyBreakdown[quarter] = qb

			// Update breakdown by symbol & term.
			key := fmt.Sprintf("%s_%s", symbol, term)
			info := taxBreakdown[key]
			info.TotalGain = info.TotalGain.Add(gainVal)
			info.TaxRate = rate.Mul(decimal.NewFromInt(100))
			info.EstimatedTax = info.EstimatedTax.Add(taxAmount)
			taxBreakdown[key] = info

			totalTax = totalTax.Add(taxAmount)

		case "Summary":
			if len(row) <= colIndex["Adjusted Gain/Loss"] {
				continue
			}
			summaryGain, err := parseMoney(row[colIndex["Adjusted Gain/Loss"]])
			if err == nil {
				taxBreakdown["summary"] = BreakdownInfo{
					TotalGain:    summaryGain,
					TaxRate:      decimal.Zero,
					EstimatedTax: decimal.Zero,
				}
			}
		}
	}

	return totalTax, taxBreakdown, quarterlyBreakdown, nil
}

// formatCurrency formats a decimal.Decimal amount as a string with thousands separators.
func formatCurrency(amount decimal.Decimal) string {
	return "$" + humanize.CommafWithDigits(amount.InexactFloat64(), 2)
}

func main() {
	flag.Parse()
	if flag.NArg() < 1 {
		fmt.Println("Usage: go run calculate-capital-gains.go path/to/G&L_Collapsed.xlsx")
		return
	}
	filePath := flag.Arg(0)
	if _, err := os.Stat(filePath); os.IsNotExist(err) {
		fmt.Printf("Error: File %s not found\n", filePath)
		return
	}

	totalTax, breakdown, quarterly, err := calculateCapitalGainsTax(filePath)
	if err != nil {
		log.Fatalf("Error processing file: %v", err)
	}

	fmt.Println("\nCapital Gains Tax Estimate Breakdown:")
	fmt.Println(strings.Repeat("-", 50))

	// If there's a summary row, show that first.
	if summary, ok := breakdown["summary"]; ok {
		fmt.Printf("Total Reported Gain (from Summary): %s\n", formatCurrency(summary.TotalGain))
		delete(breakdown, "summary")
	}

	// Show breakdown by symbol and term.
	for key, data := range breakdown {
		parts := strings.Split(key, "_")
		if len(parts) != 2 {
			continue
		}
		fmt.Printf("\nSymbol: %s, Term: %s\n", parts[0], parts[1])
		fmt.Printf("  Total Gain: %s | Tax Rate: %.1f%% | Estimated Tax: %s\n",
			formatCurrency(data.TotalGain),
			data.TaxRate.InexactFloat64(),
			formatCurrency(data.EstimatedTax))
	}

	fmt.Println("\nQuarterly Tax Breakdown:")
	fmt.Println(strings.Repeat("-", 50))
	quarters := []string{"Q1", "Q2", "Q3", "Q4"}
	for _, q := range quarters {
		qb := quarterly[q]
		if qb.TotalTax.IsZero() {
			continue
		}
		fmt.Printf("\n%s: Total Gains: %s | Estimated Tax: %s\n",
			q, formatCurrency(qb.TotalGain), formatCurrency(qb.TotalTax))
		fmt.Println("  Transactions:")
		for _, txn := range qb.Transactions {
			fmt.Printf("    %s (%s) - Date: %s | Gain: %s | Tax: %s\n",
				txn.Symbol,
				txn.Term,
				txn.Date.Format("2006-01-02"),
				formatCurrency(txn.Gain),
				formatCurrency(txn.Tax))
		}
	}

	fmt.Println(strings.Repeat("-", 50))
	fmt.Printf("Total Estimated Tax: %s\n", formatCurrency(totalTax))

	// Estimated due dates for quarterly taxes (U.S. typical).
	fmt.Println("\nEstimated Quarterly Tax Payments:")
	fmt.Println(strings.Repeat("-", 50))
	dueDates := map[string]string{
		"Q1": "April 15",
		"Q2": "June 15",
		"Q3": "September 15",
		"Q4": "January 15",
	}
	for _, q := range quarters {
		qb := quarterly[q]
		if qb.TotalTax.IsZero() {
			continue
		}
		fmt.Printf("%s (Typically due %s): %s\n", q, dueDates[q], formatCurrency(qb.TotalTax))
	}

	fmt.Println("\nNote: This is an estimate only. Please consult a tax professional for accurate tax advice.")
}