Public/Export-BudgetToExcel.ps1
|
function Export-BudgetToExcel { <# .SYNOPSIS Exports budget data to an Excel workbook. .DESCRIPTION Exports budget data to a formatted Excel workbook with multiple worksheets including accounts, billers, earnings, projected transactions, and a summary dashboard. .PARAMETER OutputPath The file path for the .xlsx export. .PARAMETER Budget Optional budget name to export. Uses active budget if not specified. .PARAMETER StartDate Start date for projected transactions. Defaults to today. .PARAMETER EndDate End date for projected transactions. Defaults to 6 months from today. .PARAMETER InitialBalance The starting balance for projected transactions. Defaults to 0. .PARAMETER IncludeSummary Include a summary dashboard sheet. Defaults to true. .PARAMETER IncludeCharts Include charts/visualizations in the workbook. .PARAMETER OpenAfterExport Open the file in Excel after creation. .EXAMPLE Export-BudgetToExcel -OutputPath "MyBudget.xlsx" Exports the active budget to an Excel workbook. .EXAMPLE Export-BudgetToExcel -OutputPath "2025-Budget.xlsx" -StartDate "2025-01-01" -EndDate "2025-12-31" -OpenAfterExport Exports with custom date range and opens the file. .EXAMPLE Export-BudgetToExcel -OutputPath "FamilyBudget.xlsx" -Budget "MyFamilyBudget" -IncludeCharts Exports a specific budget with charts. .OUTPUTS File path of the exported Excel workbook #> [CmdletBinding()] param( [Parameter(Mandatory)] [ValidatePattern('\.xlsx$')] [string]$OutputPath, [Parameter()] [string]$Budget, [Parameter()] [datetime]$StartDate = (Get-Date), [Parameter()] [datetime]$EndDate = (Get-Date).AddMonths(6), [Parameter()] [decimal]$InitialBalance = 0, [Parameter()] [switch]$IncludeSummary = $true, [Parameter()] [switch]$IncludeCharts, [Parameter()] [switch]$OpenAfterExport, [Parameter()] [string]$DataPath ) # Check if ImportExcel module is available if (-not (Get-Module -ListAvailable -Name ImportExcel)) { Write-Error @" ImportExcel module is required for Excel export functionality. Install it with: Install-Module -Name ImportExcel -Scope CurrentUser ImportExcel does not require Microsoft Excel to be installed. "@ return } Import-Module ImportExcel -ErrorAction Stop # Ensure directory exists $directory = Split-Path -Path $OutputPath -Parent if ($directory -and -not (Test-Path $directory)) { New-Item -Path $directory -ItemType Directory -Force | Out-Null } # Remove existing file if present if (Test-Path $OutputPath) { Remove-Item -Path $OutputPath -Force } # Get budget parameters $budgetParams = @{} if ($Budget) { $budgetParams['Budget'] = $Budget } if ($DataPath) { $budgetParams['DataPath'] = $DataPath } # Get budget info for summary $budgetName = if ($Budget) { $Budget } else { (Get-ActiveBudget).Name } # Build account lookup hashtable for resolving AccountId to Name $accounts = Get-Account @budgetParams $accountLookup = @{} if ($accounts) { foreach ($account in $accounts) { $accountLookup[$account.Id] = $account.Name } } try { # === Sheet 1: Summary (if enabled) === if ($IncludeSummary) { Write-Verbose "Creating Summary sheet..." $billers = Get-Biller @budgetParams $earnings = Get-Earning @budgetParams $transfers = Get-Transfer @budgetParams -ErrorAction SilentlyContinue $transactions = Get-ProjectedTransactions -StartDate $StartDate -EndDate $EndDate -InitialBalance $InitialBalance @budgetParams # Calculate statistics $totalIncome = ($transactions | Where-Object { $_.Type -eq 'Earning' } | Measure-Object -Property Amount -Sum).Sum $totalExpenses = ($transactions | Where-Object { $_.Type -eq 'Biller' } | Measure-Object -Property Amount -Sum).Sum $netCashFlow = $totalIncome + $totalExpenses # Expenses are negative $endingBalance = if ($transactions.Count -gt 0) { $transactions[-1].Balance } else { $InitialBalance } $lowestBalance = ($transactions | Measure-Object -Property Balance -Minimum).Minimum $transferCount = if ($transfers) { $transfers.Count } else { 0 } $summaryData = @( [PSCustomObject]@{ Item = 'Budget Name'; Value = $budgetName } [PSCustomObject]@{ Item = 'Export Date'; Value = (Get-Date).ToString('yyyy-MM-dd HH:mm:ss') } [PSCustomObject]@{ Item = 'Projection Period'; Value = "$($StartDate.ToString('yyyy-MM-dd')) to $($EndDate.ToString('yyyy-MM-dd'))" } [PSCustomObject]@{ Item = ''; Value = '' } [PSCustomObject]@{ Item = 'Total Accounts'; Value = $accounts.Count } [PSCustomObject]@{ Item = 'Total Billers'; Value = $billers.Count } [PSCustomObject]@{ Item = 'Total Earnings'; Value = $earnings.Count } [PSCustomObject]@{ Item = 'Total Transfers'; Value = $transferCount } [PSCustomObject]@{ Item = 'Total Transactions'; Value = $transactions.Count } [PSCustomObject]@{ Item = ''; Value = '' } [PSCustomObject]@{ Item = 'Starting Balance'; Value = $InitialBalance } [PSCustomObject]@{ Item = 'Total Projected Income'; Value = $totalIncome } [PSCustomObject]@{ Item = 'Total Projected Expenses'; Value = $totalExpenses } [PSCustomObject]@{ Item = 'Net Cash Flow'; Value = $netCashFlow } [PSCustomObject]@{ Item = 'Ending Balance'; Value = $endingBalance } [PSCustomObject]@{ Item = 'Lowest Balance'; Value = $lowestBalance } ) $summaryData | Export-Excel -Path $OutputPath -WorksheetName 'Summary' -AutoSize -Title "Budget Summary: $budgetName" -TitleBold -TitleSize 16 } # === Sheet 2: Accounts === Write-Verbose "Creating Accounts sheet..." $accounts = Get-Account @budgetParams if ($accounts -and $accounts.Count -gt 0) { $accountsExport = $accounts | Select-Object Name, Bank, Last4Digits, Id $accountsExport | Export-Excel -Path $OutputPath -WorksheetName 'Accounts' -AutoSize -TableName 'Accounts' -TableStyle Medium2 } else { [PSCustomObject]@{ Message = 'No accounts configured' } | Export-Excel -Path $OutputPath -WorksheetName 'Accounts' -AutoSize } # === Sheet 3: Earnings === Write-Verbose "Creating Earnings sheet..." $earnings = Get-Earning @budgetParams if ($earnings -and $earnings.Count -gt 0) { # Calculate annual amounts using helper and add Account name $earningsWithAnnual = $earnings | ForEach-Object { $accountName = if ($_.AccountId -and $accountLookup.ContainsKey($_.AccountId)) { $accountLookup[$_.AccountId] } else { '' } [PSCustomObject]@{ Name = $_.Name Account = $accountName Amount = $_.Amount Frequency = $_.Frequency StartDate = $_.StartDate AnnualAmount = Get-CalculatedAmount -Amount $_.Amount -Frequency $_.Frequency -TargetPeriod Annual Id = $_.Id } } $earningsWithAnnual | Export-Excel -Path $OutputPath -WorksheetName 'Earnings' -AutoSize -TableName 'Earnings' -TableStyle Medium4 } else { [PSCustomObject]@{ Message = 'No earnings configured' } | Export-Excel -Path $OutputPath -WorksheetName 'Earnings' -AutoSize } # === Sheet 4: Billers === Write-Verbose "Creating Billers sheet..." $billers = Get-Biller @budgetParams if ($billers -and $billers.Count -gt 0) { # Calculate annual amounts using helper and add Account name $billersWithAnnual = $billers | ForEach-Object { $accountName = if ($_.AccountId -and $accountLookup.ContainsKey($_.AccountId)) { $accountLookup[$_.AccountId] } else { '' } [PSCustomObject]@{ Name = $_.Name Account = $accountName Amount = $_.Amount Frequency = $_.Frequency StartDate = $_.StartDate AnnualAmount = Get-CalculatedAmount -Amount $_.Amount -Frequency $_.Frequency -TargetPeriod Annual Id = $_.Id } } $billersWithAnnual | Export-Excel -Path $OutputPath -WorksheetName 'Billers' -AutoSize -TableName 'Billers' -TableStyle Medium3 } else { [PSCustomObject]@{ Message = 'No billers configured' } | Export-Excel -Path $OutputPath -WorksheetName 'Billers' -AutoSize } # === Sheet 5: Transfers === Write-Verbose "Creating Transfers sheet..." $transfers = Get-Transfer @budgetParams -ErrorAction SilentlyContinue if ($transfers -and $transfers.Count -gt 0) { # Calculate annual amounts and resolve account names $transfersWithDetails = $transfers | ForEach-Object { $fromAccountName = if ($_.FromAccountId -and $accountLookup.ContainsKey($_.FromAccountId)) { $accountLookup[$_.FromAccountId] } else { '' } $toAccountName = if ($_.ToAccountId -and $accountLookup.ContainsKey($_.ToAccountId)) { $accountLookup[$_.ToAccountId] } else { '' } [PSCustomObject]@{ Name = $_.Name FromAccount = $fromAccountName ToAccount = $toAccountName Amount = $_.Amount Frequency = $_.Frequency StartDate = $_.StartDate AnnualAmount = Get-CalculatedAmount -Amount $_.Amount -Frequency $_.Frequency -TargetPeriod Annual Id = $_.Id } } $transfersWithDetails | Export-Excel -Path $OutputPath -WorksheetName 'Transfers' -AutoSize -TableName 'Transfers' -TableStyle Medium5 } else { [PSCustomObject]@{ Message = 'No transfers configured' } | Export-Excel -Path $OutputPath -WorksheetName 'Transfers' -AutoSize } # === Sheet 6: Projected Transactions === Write-Verbose "Creating Projected Transactions sheet..." $transactions = Get-ProjectedTransactions -StartDate $StartDate -EndDate $EndDate -InitialBalance $InitialBalance @budgetParams if ($transactions -and $transactions.Count -gt 0) { $transactionsExport = $transactions | Select-Object @{N='Date';E={$_.Date.ToString('yyyy-MM-dd')}}, Name, Type, Amount, Balance # Export with conditional formatting $excel = $transactionsExport | Export-Excel -Path $OutputPath -WorksheetName 'Transactions' -AutoSize -TableName 'Transactions' -TableStyle Medium1 -PassThru $ws = $excel.Workbook.Worksheets['Transactions'] $lastRow = $ws.Dimension.End.Row # Conditional formatting for Amount column (column D) - Green for positive, Red for negative $amountColumn = 4 # Amount is 4th column Add-ConditionalFormatting -Worksheet $ws -Range "D2:D$lastRow" -RuleType GreaterThan -ConditionValue 0 -ForegroundColor Green Add-ConditionalFormatting -Worksheet $ws -Range "D2:D$lastRow" -RuleType LessThan -ConditionValue 0 -ForegroundColor Red # Conditional formatting for Balance column (column E) - Red if negative Add-ConditionalFormatting -Worksheet $ws -Range "E2:E$lastRow" -RuleType LessThan -ConditionValue 0 -ForegroundColor White -BackgroundColor Red Close-ExcelPackage $excel } else { [PSCustomObject]@{ Message = 'No transactions in date range' } | Export-Excel -Path $OutputPath -WorksheetName 'Transactions' -AutoSize } # === Optional: Charts === if ($IncludeCharts -and $transactions -and $transactions.Count -gt 0) { Write-Verbose "Creating charts..." # Monthly summary for charts $monthlySummary = $transactions | Group-Object { $_.Date.ToString('yyyy-MM') } | ForEach-Object { $monthTransactions = $_.Group $income = ($monthTransactions | Where-Object { $_.Type -eq 'Earning' } | Measure-Object -Property Amount -Sum).Sum $expenses = [Math]::Abs(($monthTransactions | Where-Object { $_.Type -eq 'Biller' } | Measure-Object -Property Amount -Sum).Sum) [PSCustomObject]@{ Month = $_.Name Income = if ($income) { $income } else { 0 } Expenses = if ($expenses) { $expenses } else { 0 } EndBalance = ($monthTransactions | Sort-Object Date | Select-Object -Last 1).Balance } } # Export monthly summary and add chart $excel = $monthlySummary | Export-Excel -Path $OutputPath -WorksheetName 'Monthly Summary' -AutoSize -TableName 'MonthlySummary' -TableStyle Medium6 -PassThru $ws = $excel.Workbook.Worksheets['Monthly Summary'] $rowCount = $monthlySummary.Count + 1 # +1 for header # Add Income vs Expenses chart Add-ExcelChart -Worksheet $ws -Title 'Income vs Expenses' -ChartType ColumnClustered ` -XRange $ws.Cells["A2:A$rowCount"] -YRange $ws.Cells["B2:C$rowCount"] ` -SeriesHeader 'Income', 'Expenses' -LegendPosition Bottom -Width 600 -Height 400 ` -Row 1 -Column 6 Close-ExcelPackage $excel } Write-Verbose "Excel export complete: $OutputPath" # Open file if requested if ($OpenAfterExport) { if ($IsWindows -or $PSVersionTable.PSVersion.Major -lt 6) { Start-Process $OutputPath } else { Write-Warning "OpenAfterExport is only supported on Windows." } } return (Resolve-Path $OutputPath).Path } catch { Write-Error "Failed to export budget to Excel: $_" } } |