PowerShell to convert downloaded transaction file from LT Trust 401k to Quicken format
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