GenerateExcelBudget
Back to ListDescription: Main function to produce the Excel budget shown in the demo app for cash flow budgets.
FileMaker Prototype:
Set Variable [$res; ACF_Run("GenerateExcelBudget"; string_BudgetKey)]
Category: EXCEL
Dependencies
- NextTransRepeat: Calculate the next date (>= minDate) for a recuring transaction.
- DefineStyles: Define the styles used in the cash-flow demo spreadsheet.
- UpdateExcelBudget: Update function for the Excel CashFlow App
NOTE: This function serves as a reference template. To use it effectively, you may need to adjust certain parts of the code, such as field names, database structures, and specific data lists to fit the requirements of your system. This customization allows the function to work seamlessly within your environment.
Function source:
/*
Main routine - Called from a button in the Budget layout.
Produce an Excel Spreadsheet for the Cash-Flow budget.
A summary tab first, and then month tabs for the needed amount of tabs to cover all months
between start and end dates in the budget record.
*/
function GenerateExcelBudget(string BudgetKey)
// Declare arrays to hold data from the SQL query
ARRAY STRING budTitle;
ARRAY STRING budSubtitle;
ARRAY DATE budFromDate;
ARRAY DATE budToDate;
ARRAY STRING budExcelPath;
ARRAY STRING traPrimaryKey;
ARRAY STRING traBudget_fk;
ARRAY STRING traName;
ARRAY DATE traDateStart;
ARRAY STRING traIncomeExpense;
ARRAY STRING traPeriodType;
ARRAY INT traPeriodCount;
ARRAY FLOAT traAmount;
ARRAY STRING traCategory;
// SQL Query to fetch budget and transaction data
string sql = "SELECT bud.Title, bud.Subtitle, bud.FromDate, bud.ToDate, bud.ExcelPath,
tra.PrimaryKey, tra.Budget_fk, tra.Name, tra.DateStart, tra.IncomeExpense, tra.PeriodType,
tra.PeriodCount, tra.Amount, tra.Category
FROM Budget AS bud
INNER JOIN Transactions AS tra ON bud.PrimaryKey = tra.Budget_fk
WHERE bud.PrimaryKey = :BudgetKey
ORDER BY tra.IncomeExpense DESC, tra.Category ASC, tra.Name ASC
INTO :budTitle, :budSubtitle, :budFromDate, :budToDate, :budExcelPath,
:traPrimaryKey, :traBudget_fk, :traName, :traDateStart, :traIncomeExpense, :traPeriodType,
:traPeriodCount, :traAmount, :traCategory";
// Execute the SQL query
string res = ExecuteSQL(sql);
int ctrans = sizeof(budExcelPath);
if (res != "OK" || ctrans == 0) then
return "No transactions: " + res; // Return if no transactions found
end if
// Have JSON copy of the data for future updates to the sheet.
// Used to compare new data, old data and data from the sheet to see what is new, what is
// user changes to the sheet, and what is the same.
JSON js;
js["budTitle"] = budTitle[1];
js["budSubtitle"] = budSubtitle[1];
js["budFromDate"] = budFromDate[1];
js["budToDate"] = budToDate[1];
js["budExcelPath"] = budExcelPath[1];
js["BudgetKey"] = BudgetKey;
js["traPrimaryKey"] = traPrimaryKey;
js["traName"] = traName;
js["traDateStart"] = traDateStart;
js["traIncomeExpense"] = traIncomeExpense;
js["traPeriodType"] = traPeriodType;
js["traPeriodCount"] = traPeriodCount;
js["traAmount"] = traAmount;
js["traCategory"] = traCategory;
// return "Debug";
// Extract date range
DATE fromDate = budFromDate[1];
DATE toDate = budToDate[1];
int mndFrom = int(string(fromDate, "%m"));
int mndTo = int(string(toDate, "%m"));
int YearFrom = int(string(fromDate, "%Y"));
int YearTo = int(string(toDate, "%Y"));
ARRAY STRING mndNames = {"January", "February", "March", "April", "May", "June",
"July", "August", "September", "October", "November", "December"};
// Create the Excel workbook
if (budExcelPath[1] == "") then
return "No Path";
end if
$$WaituSec = 0.0; // Calculate the microseconds spent on the confirm dialogue (User answer wait)
long uSecStart, uSecEnd;
if (file_exists(budExcelPath[1])) then
uSecStart = uSec();
if (confirm ("Update the file: " + budExcelPath[1])) then
uSecEnd = uSec();
$$WaituSec = uSecEnd-uSecStart;
return UpdateExcelBudget(BudgetKey);
else
uSecEnd = uSec();
$$WaituSec = uSecEnd-uSecStart;
return "Update canceled";
end if
end if
// Update jsCopy so we have some values to compare next time we update the Workbook
string jsCopy = string (js);
res = ExecuteSQL ( "UPDATE Budget SET jsCopy = :jsCopy WHERE PrimaryKey = :BudgetKey");
int wb = Excel_create(budExcelPath[1], "Summary");
int s, sumary = Excel_getSheetID(wb, 1);
res = DefineStyles(wb);
int noTabs = 0;
int i, yy, mm, immf, immt, dim, dow, crow, position, sec1row, sec2row;
DATE fim, lim, cdate;
string chStyle, formel, PrevSheetRef, sheetName;
// Initialize the summary sheet
excel_setColumnWidth(sumary, 1, 20.0);
Excel_SetCell(sumary, 1, 1, "Summary sheet: " + budTitle[1], "H1");
Excel_SetCell(sumary, 2, 1, budSubtitle[1] + " " + string(fromDate) + " - " + string(toDate), "H2");
// Add starting balance
Excel_SetCell(sumary, 3, 1, "Balance-in:", "CH");
Excel_SetCell(sumary, 3, 2, 0.0, "SumLine");
// Add month headings to summary sheet
int scol = 2;
for (yy = YearFrom, YearTo)
immf = ((yy == YearFrom) ? mndFrom : 1);
immt = ((yy == YearTo) ? mndTo : 12);
for (mm = immf, immt)
cdate = date(format("%04d-%02d-%02d", yy, mm, 1), "%Y-%m-%d");
Excel_setCell(sumary, 5, scol, cdate, "SCH");
scol++;
end for
end for
// Add summary sheet titles for balance, income, and expenses
Excel_SetCell(sumary, 6, 1, "MAX balance:", "CH");
Excel_SetCell(sumary, 7, 1, "MIN balance:", "CH");
Excel_SetCell(sumary, 8, 1, "Estimated Income:", "CH");
Excel_SetCell(sumary, 9, 1, "Estimated Expense:", "CH");
scol = 2;
for (yy = YearFrom, YearTo)
immf = ((yy == YearFrom) ? mndFrom : 1);
immt = ((yy == YearTo) ? mndTo : 12);
for (mm = immf, immt)
// Create a new sheet for the month
sheetName = mndNames[mm] + "-" + yy;
s = Excel_Add_WorkSheet(wb, sheetName);
excel_setColumnWidth(s, 1, 20.0);
Excel_SetCell(s, 1, 1, budTitle[1], "H1");
Excel_SetCell(s, 2, 1, budSubtitle[1] + " " + mndNames[mm] + "-" + yy, "H2");
// Handle first page balance reference
if (noTabs == 0) then
PrevSheetRef = Excel_SheetAddress("Summary", 3, 2);
end if
noTabs++;
// Determine first and last day of the month
fim = date(format("%04d-%02d-%02d", yy, mm, 1), "%Y-%m-%d");
if ( mm == 12 ) then
// special for december, as month 13 does not exists.
lim = date(format("%04d-%02d-%02d", yy, mm , 31), "%Y-%m-%d");
else
lim = date(format("%04d-%02d-%02d", yy, mm + 1, 1), "%Y-%m-%d") - 1;
end if
dim = lim - fim + 1;
// Create date headings for the sheet
for (i = 1, dim)
cdate = fim + (i - 1);
dow = string(cdate, "%w");
dow = ((dow == 0) ? 7 : dow);
case
:(dow == 6) chStyle = "CHSAT";
:(dow == 7) chStyle = "CHSUN";
default chStyle = "CH";
end case
excel_SetCell(s, 3, i + 1, cdate, chStyle);
end for
crow = 5;
// Process transactions: Income
sec1row = crow;
for (i = 1, ctrans)
if (left(traIncomeExpense[i], 1) == "I") then
cdate = traDateStart[i];
Excel_SetCell(s, crow, 1, traName[i], "num");
res = styleRow ( s, crow, dim+1);
repeat
if (cdate <= lim && cdate >= fim) then
position = cdate - fim;
Excel_SetCell(s, crow, position + 2, traAmount[i], "num");
end if
cdate = NextTransRepeat(cdate, fim, traDateStart[i], traPeriodType[i], traPeriodCount[i]);
until (cdate > lim);
crow++;
end if
end for
crow++;
// Add formulas for income totals
excel_setCell(s, crow, 1, "Sum Income", "SumInc");
for (i = 1, dim)
formel = format("SUM(%s)", excel_sheetaddress(sec1row, i + 1, crow - 1, i + 1));
Excel_setCellFormula(s, crow, i + 1, formel, "SumInc");
end for
sec1row = crow;
crow++;
// Process transactions: Expenses
sec2row = crow;
for (i = 1, ctrans)
if (left(traIncomeExpense[i], 1) == "E") then
cdate = traDateStart[i];
Excel_SetCell(s, crow, 1, traName[i], "num");
res = styleRow ( s, crow, dim);
repeat
if (cdate <= lim && cdate >= fim) then
position = cdate - fim;
Excel_SetCell(s, crow, position + 2, traAmount[i], "num");
end if
cdate = NextTransRepeat(cdate, fim, traDateStart[i], traPeriodType[i], traPeriodCount[i]);
until (cdate > lim);
crow++;
end if
end for
crow++;
// Add formulas for expense totals
excel_setCell(s, crow, 1, "Sum Expenses", "SumExp");
for (i = 1, dim)
formel = format("SUM(%s)", excel_sheetaddress(sec2row, i + 1, crow - 1, i + 1));
Excel_setCellFormula(s, crow, i + 1, formel, "SumExp");
end for
sec2row = crow;
crow++; crow++;
// Add formulas for balance calculation
// first day is special
excel_setCell(s, crow, 1, "Balance", "SumBal");
formel = format("%s-%s+%s", excel_sheetaddress(sec1row, 2), excel_sheetaddress(sec2row, 2), PrevSheetRef);
Excel_setCellFormula(s, crow, 2, formel, "SumBal");
// The remaining days
for (i = 2, dim)
formel = format("%s-%s+%s", excel_sheetaddress(sec1row, i + 1), excel_sheetaddress(sec2row, i + 1), excel_sheetaddress(crow, i));
Excel_setCellFormula(s, crow, i + 1, formel, "SumBal");
end for
// Add a notes row, and style it with textwrap and alignment top.
Excel_SetCell ( s, crow+3, 1, "Notes:", "H2");
for (i=2, dim)
Excel_SetCell ( s, crow+3, i, "", "Note");
end for
// Update summary sheet formulas
formel = format("MAX(%s)", excel_sheetaddress(sheetName, crow, 2, crow, dim + 1));
Excel_SetCellFormula(sumary, 6, scol, formel, "SumBal");
formel = format("MIN(%s)", excel_sheetaddress(sheetName, crow, 2, crow, dim + 1));
Excel_SetCellFormula(sumary, 7, scol, formel, "SumBal");
formel = format("SUM(%s)", excel_sheetaddress(sheetName, sec1row, 2, sec1row, dim + 1));
Excel_SetCellFormula(sumary, 8, scol, formel, "SumInc");
formel = format ("SUM(%s)", excel_sheetaddress (sheetName, sec2row,2, sec2row, dim+1));
Excel_SetCellFormula ( sumary, 9, scol, formel, "SumExp");
scol++;
// The reference to include in the next month sheet to carry over the balance.
PrevSheetRef = Excel_SheetAddress (sheetName, crow, dim+1);
end for // Months
end for // Years
Excel_Close(wb); // Close and save the Workbook
return "OK";
end