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.
Tags: 401k, LT Trust, PowerShell, Quicken
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