Last active
November 5, 2023 16:00
-
-
Save defron/4132c7cd14aa1ac225b5b9ae58b6b2b4 to your computer and use it in GitHub Desktop.
Daily Balance based on current balance from mint transactions export
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <# | |
| .SYNOPSIS | |
| Helps calculate daily balances from a mint transaction export | |
| .DESCRIPTION | |
| The Daily-Balances.ps1 script uses your current Balance and | |
| an ACCOUNT-SPECIFIC export to calculate daily balances. | |
| The Current Balance passed in will be set for the first date in the input csv file | |
| and the script works backwards to the oldest date in the input csv file | |
| .PARAMETER CurrentBalance | |
| The current balance of the account as a decimal. | |
| For checking/savings/investment accounts, this number should be positive unless the account is overdrawn | |
| For credit card/loan accounts, this number should be negative if you owe a balance unless you use the -Invert switch | |
| .PARAMETER InputCsv | |
| Specifies the path to the CSV-based input file. | |
| Can be in the same directory (just give the file name) or a relative/absolute path | |
| .PARAMETER OutputCsv | |
| Specifies the name and path for the CSV-based output file. By default, | |
| Daily-Balances.ps1 puts the csv file in the current working directory | |
| .PARAMETER Delimiter | |
| Optional CSV delimiter for the input csv. Defaults to ',' | |
| which seems to be Mint's default delimiter | |
| .PARAMETER SkipEmptyDays | |
| By default Daily-Balances.ps1 will give a running daily balance for | |
| EVERY DAY between the most recent date in the csv to the earliest date in the csv | |
| This switch lets you override that to only output days that actually have transactions | |
| .PARAMETER Invert | |
| By default Daily-Balances.ps1 treat credits as cash inflows and debits as cash outflows | |
| Invert reverses this which is useful for credit card/loans if you want to keep balances positive instead of negative | |
| .INPUTS | |
| None. You can't pipe objects to Daily-Balances.ps1 | |
| .OUTPUTS | |
| None. Daily-Balances.ps1 doesn't generate any output. | |
| .EXAMPLE | |
| PS> ./Daily-Balances.ps1 -CurrentBalance 1571.93 -InputCsv transactions-chase-savings.csv -OutputCsv chase-savings-daily-balances.csv | |
| .EXAMPLE | |
| PS> ./Daily-Balances.ps1 -CurrentBalance -3230.93 -InputCsv transactions-chase-sapphire-reserve.csv -OutputCsv chase-sapphire-reserve-daily-balances.csv -SkipEmptyDays | |
| .EXAMPLE | |
| PS> ./Daily-Balances.ps1 -CurrentBalance 3230.93 -InputCsv transactions-chase-sapphire-reserve.csv -OutputCsv chase-sapphire-reserve-daily-balances.csv -SkipEmptyDays -Invert | |
| .EXAMPLE | |
| PS> .\Daily-Balances.ps1 -CurrentBalance 3230.93 -InputCsv C:\Users\Foo\Desktop\transactions-chase-checking.csv -OutputCsv C:\Users\Foo\Desktop\chase-checking-daily-balances.csv | |
| #> | |
| param ( | |
| [Parameter(Mandatory=$true)] | |
| [decimal]$CurrentBalance, | |
| [Parameter(Mandatory=$true)] | |
| [string]$InputCsv, | |
| [Parameter(Mandatory=$true)] | |
| [string]$OutputCsv, | |
| [char]$Delimiter=',', | |
| [switch]$SkipEmptyDays=$false, | |
| [switch]$Invert=$false | |
| ) | |
| $data = Import-Csv -Delimiter $Delimiter $InputCsv | |
| $currentDate = Get-Date $data[0].Date | |
| $lastDate = Get-Date $data[$data.length - 1].Date | |
| $dailyBalance = $CurrentBalance | |
| $balances = @([pscustomobject]@{Date=$data[0].Date;Balance=$CurrentBalance}) | |
| if ( -not $SkipEmptyDays) { | |
| for ($currentDate; $currentDate -ge $lastDate; $currentDate = $currentDate.AddDays(-1)) { | |
| $lookupDate = $currentDate.ToString('M/dd/yyyy') | |
| $dailyRecords = $data | ? {$_.Date -eq $lookupDate} | |
| if($dailyRecords.length -gt 0) { | |
| $dailyRecords | % { | |
| $amount = If ($Invert) {[decimal]$_.Amount * -1} Else {[decimal]$_.Amount} | |
| if($_."Transaction Type".ToLower() -eq 'credit'){ | |
| $dailyBalance -= $amount | |
| } else { | |
| $dailyBalance += $amount | |
| } | |
| } | |
| } | |
| $date = $currentDate.AddDays(-1).ToString('M/dd/yyyy') | |
| $balances += [pscustomobject]@{Date=$date;Balance=$dailyBalance} | |
| } | |
| } else { | |
| $dates = $data | % { $_.Date } | Get-Unique | |
| for ($dateIdx = 0; $dateIdx -lt $dates.length; $dateIdx += 1) { | |
| $results = $data | ? {$_.Date -eq $dates[$dateIdx]} | |
| $results | % { | |
| $amount = If ($Invert) {[decimal]$_.Amount * -1} Else {[decimal]$_.Amount} | |
| if($_."Transaction Type".ToLower() -eq 'credit'){ | |
| $dailyBalance -= $amount | |
| } else { | |
| $dailyBalance += $amount | |
| } | |
| } | |
| $oldestRecordIndex = [array]::IndexOf($data, $results[$results.length - 1]) | |
| $date = If ($oldestRecordIndex -lt $data.length - 1) {$data[$oldestRecordIndex + 1].Date} Else {(Get-Date $dates[$dateIdx]).AddDays(-1).ToString('M/dd/yyyy')} | |
| $balances += [pscustomobject]@{Date=$date;Balance=$dailyBalance} | |
| } | |
| } | |
| $balances | Export-Csv $OutputCsv |
Author
Author
Added an -Invert flag for if you want to keep cc balances positive instead of negative
Also sped up the SkipEmptyDays option
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
I decided to write this in powershell since most people should have powershell installed. I don't think I included any newer syntax but I only have pwsh 7.3 (and am running linux but it should work fine on Windows)
Note that if you run into issues running this script because "Running scripts is disabled on this system" then you need to change your powershell executionpolicy:
Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser