Go code to parse multi-currency stock portfolio purchases for ledger-cli

ledger.go

package utils

import (
	"bufio"
	"fmt"
	"os"
	"regexp"
	"strconv"
	"strings"
	"time"

	"github.com/wakatara/zoidberg/config"
	"github.com/wakatara/zoidberg/models"
)

// ParseLedgerFile reads the ledger file and inserts transactions into the database
func ParseLedgerFile(filePath string, portfolioID uint) error {
	file, err := os.Open(filePath)
	if err != nil {
		return err
	}
	defer file.Close()

	scanner := bufio.NewScanner(file)
	var date time.Time
	var description string
	transactionRegex := regexp.MustCompile(`Assets:([^\s]+)\s+(\d+(?:\.\d+)?)\s+([^\s]+)\s*@\s*(\d+(?:\.\d+)?)\s*([A-Z]{3})`)
	cashRegex := regexp.MustCompile(`Assets:.*Cash.*-([\d\.]+)\s+([A-Z]{3})\s*(?:@\s*([\d\.]+)\s*([A-Z]{3}))?`)

	for scanner.Scan() {
		line := scanner.Text()

		// Skip comments and empty lines
		if strings.HasPrefix(line, "#") || strings.TrimSpace(line) == "" {
			continue
		}

		// Detect new transaction entry by parsing date
		if match, _ := regexp.MatchString(`^\d{4}-\d{2}-\d{2}`, line); match {
			parts := strings.SplitN(line, " ", 2)
			if len(parts) < 2 {
				continue
			}

			parsedDate, err := time.Parse("2006-01-02", parts[0])
			if err != nil {
				return err
			}
			date = parsedDate
			description = strings.Split(parts[1], ";")[0] // Remove comments
			continue
		}

		// Parse transaction lines
		if matches := transactionRegex.FindStringSubmatch(line); matches != nil {
			symbol := matches[1]
			quantity, _ := strconv.ParseFloat(matches[2], 64)
			currency := matches[5] // Ensure this captures the correct currency
			unitCost, _ := strconv.ParseFloat(matches[4], 64)

			var baseCurrency string
			var totalCost float64
			currencyToBaseRate := 1.0

			// Check next line for cash transaction
			if scanner.Scan() {
				cashLine := scanner.Text()
				if cashMatches := cashRegex.FindStringSubmatch(cashLine); cashMatches != nil {
					totalCost, _ = strconv.ParseFloat(cashMatches[1], 64)
					baseCurrency = cashMatches[2]

					if len(cashMatches) > 3 && cashMatches[3] != "" {
						currencyToBaseRate, _ = strconv.ParseFloat(cashMatches[3], 64)
						currencyToBaseRate = 1 / currencyToBaseRate
					}
				}
			}

			// Save transaction
			tx := models.Transaction{
				PortfolioID:           portfolioID,
				Date:                  date,
				Symbol:                symbol,
				Type:                  models.Buy,
				UnitCost:              unitCost,
				Currency:              currency,
				Quantity:              quantity,
				FixedCost:             0,
				BaseCurrency:          baseCurrency,
				CurrencyToBaseRate:    currencyToBaseRate,
				TotalBaseCurrencyCost: totalCost,
				Description:           description,
				Account:               "Assets:CA:Cash",
			}

			config.Db.Create(&tx)
		}
	}

	return scanner.Err()
}

// ExportToLedger exports transactions to a ledger format
func ExportToLedger(filePath string) error {
	file, err := os.Create(filePath)
	if err != nil {
		return err
	}
	defer file.Close()

	writer := bufio.NewWriter(file)

	var transactions []models.Transaction
	config.Db.Find(&transactions)

	for _, tx := range transactions {
		// Write transaction header line
		header := fmt.Sprintf("%s %s\n", tx.Date.Format("2006-01-02"), tx.Description)
		_, err := writer.WriteString(header)
		if err != nil {
			return err
		}

		// Write asset line
		assetLine := fmt.Sprintf("    Assets:%s    %.2f %s @ %.4f %s\n",
			tx.Symbol, tx.Quantity, tx.Symbol, tx.UnitCost, tx.Currency)
		_, err = writer.WriteString(assetLine)
		if err != nil {
			return err
		}

		// Write cash transaction line
		cashLine := fmt.Sprintf("    Assets:CA:Cash    %.2f %s",
			-tx.TotalBaseCurrencyCost, tx.BaseCurrency)
		if tx.Currency != tx.BaseCurrency {
			cashLine += fmt.Sprintf(" @ %.8f %s", 1/tx.CurrencyToBaseRate, tx.Currency)
		}
		cashLine += "\n\n"

		_, err = writer.WriteString(cashLine)
		if err != nil {
			return err
		}
	}

	writer.Flush()
	return nil
}

models.go

// Transaction represents a cost basis lot
type Transaction struct {
	gorm.Model
	PortfolioID           uint            `json:"portfolio_id"`
	Date                  time.Time       `gorm:"index:idx_txn" json:"date"`
	Symbol                string          `gorm:"index:idx_symbol" json:"symbol"`
	Type                  TransactionType `gorm:"index:idx_txntype" json:"type"`
	UnitCost              float64         `json:"unit_cost"`
	Currency              string          `gorm:"index:idx_currency" json:"currency"`
	Quantity              float64         `json:"quantity"`
	FixedCost             float64         `json:"fixed_cost"`
	BaseCurrency          string          `gorm:"index:idx_base_currency" json:"base_currency"`
	CurrencyToBaseRate    float64         `json:"currency_to_base_rate"`
	TotalBaseCurrencyCost float64         `json:"total_base_currency_cost"`
	Description           string          `json:"description"` // Added for ledger notes
	Account               string          `gorm:"index:idx_account" json:"account"`
}