Jamie's Blog

FYI: This assumes a 100% vesting from the employer provided funds. It lumps all the trade types together and gives a final amount. It helps get it close enough but there will be a few cents$ left over as LT Trusts export rounding is not highly accurate for some reason.
Update the file paths below to match the downloaded file location and output location.

$downloadedTransactionsFile = "C:\Users\<<NAME>>\Downloads\export.csv";
$exportedFile = "C:\Users\<<NAME>>\Desktop\import-lt.csv"


# NO TOUCH
Write-Output "Removing old cleaned file: $exportedFile"
$null = Remove-Item -Path $exportedFile -Force:$true -Confirm:$false -ErrorAction SilentlyContinue
#read downloaded csv file
$transactions = Get-Content -Path $downloadedTransactionsFile;

#convert csv into psobject
$trades = ConvertFrom-Csv $transactions;
Write-Output "Converted: $($trades.Count) items from CSV";

$buys = @();
$sells = @();
$divs = @();

#split all the trades by type
foreach ($trade in $trades) {
    if ($trade.action -eq "Buy") {
        $buys += $trade;
    }
    elseif ($trade.action -eq "Sell") {
        $sells += $trade;
    }
    elseif ($trade.action -eq "REINVDIV") {
        $divs += $trade;
    }
}
#verify counts
Write-Output "Found $($buys.Count) buy trades";
Write-Output "Found $($sells.Count) sell trades";
Write-Output "Found $($divs.Count) div trades";
Write-Output "Total trades found: $($buys.Count + $sells.Count + $divs.Count)";

#Group trades by security
$buys = $buys | Group-Object -Property Security -AsHashTable;
$sells = $sells | Group-Object -Property Security -AsHashTable;
$divs = $divs | Group-Object -Property Security -AsHashTable;

$cleanedBuys = @();
$cleanedSells = @();
$cleanedDivs = @();

$cleanedTrade = [ordered] @{
    Action   = ""
    Security = ""
    Quantity = 0
    Price    = 0
    Amount   = 0
}

Write-Output "Cleaning and preparing to export"
foreach ($buy in $buys.Keys) {
    Write-Debug "Creating: $buy buy object"
    $buyObj = New-Object psobject -Property $cleanedTrade
    $buyObj.Security = $buy
    $buyObj.Action = "Buy"
    foreach ($security in $buys[$buy]) {
        $buyObj.Quantity = ($buyObj.Quantity + ($security.Quantity -replace '[^\d.]'))
        $buyObj.Amount = ($buyObj.Amount + ($security.Amount -replace '[^\d.]'))
        $buyObj.Price = $security.Price -replace '[^\d.]'
    }
    $cleanedBuys += $buyObj
    Export-Csv -InputObject $buyObj -Path $exportedFile -NoClobber -Append -NoTypeInformation
}

foreach ($sell in $sells.Keys) {
    Write-Debug "Creating: $sell sell object"
    $sellObj = New-Object psobject -Property $cleanedTrade
    $sellObj.Security = $sell
    $sellObj.Action = "Sell"
    foreach ($security in $sells[$sell]) {
        $sellObj.Quantity = ($sellObj.Quantity + ($security.Quantity -replace '[^\d.]'))
        $sellObj.Amount = ($sellObj.Amount + ($security.Amount -replace '[^\d.]'))
        $sellObj.Price = $security.Price -replace '[^\d.]'
    }
    $cleanedSells += $sellObj
    Export-Csv -InputObject $sellObj -Path $exportedFile -NoClobber -Append -NoTypeInformation
}

foreach ($div in $divs.Keys) {
    Write-Debug "Creating: $div div object"
    $divObj = New-Object psobject -Property $cleanedTrade
    $divObj.Security = $div
    $divObj.Action = "ReinvDiv"
    foreach ($security in $divs[$div]) {
        $divObj.Quantity = ($divObj.Quantity + ($security.Quantity -replace '[^\d.]'))
        $divObj.Amount = ($divObj.Amount + ($security.Amount -replace '[^\d.]'))
        $divObj.Price = $security.Price -replace '[^\d.]'
    }
    $cleanedDivs += $divObj
    Export-Csv -InputObject $divObj -Path $exportedFile -NoClobber -Append -NoTypeInformation
}

Write-Output "Exported: $($cleanedBuys.Count) buys"
Write-Output "Exported: $($cleanedSells.Count) sells"
Write-Output "Exported: $($cleanedDivs.Count) divs"
Write-Output "Total exported: $($cleanedBuys.Count + $cleanedSells.Count + $cleanedDivs.Count) items."
Write-Output "We are now complete, file exported: $exportedFile" 
$null = Remove-Item -Path $downloadedTransactionsFile -Force:$true -Confirm:$false -ErrorAction SilentlyContinue

Tags: 401k, LT Trust, PowerShell, Quicken