SP.FINANCIALS_AGG

SP.FINANCIALS_AGG works identically to SP.FINANCIALS, however excepts a set of hotel codes and pulls an aggregated value for the list of hotels provided. Just like SP.FINANCIALS it allows you to pull data from the singlepane database for any financial/GL account in our chart of accounts. This includes data from hotel P&Ls, budgets, monthly forecasts and owner and operator pro-formas.

SP.FINANCIALS_AGG has the following arguments, which can either be entered directly into the formulas surrounded by quotation marks, or entered by cell reference (A1).

Property Codes - a list of singlepane property codes, usually three letters. All property codes that are authorized under your account can be found in the "My Properties" excel tab that is automatically created in the workbook when you log-in to the add-in. This list of codes can be provided one of three ways:

1. Range Reference (A1:A3) with codes in each of the cells.

2. Hardcoded Quoted List ("ABC, DEF, GHI")

3. Nested SP.FILTER() function - see related knowledge base post.

USALI - the singlepane GL account code that you would like to pull data for. All available USALI accounts are found in the "Usali Reference" tab that is automatically created in the workbook when you log-in to the add-in.

Month - A three letter month abbreviation (i.e. "Jul" for July). You can also pull the following time aggregations:

• Total Year

• Q1, Q2, Q3, Q4

• MMMYTD (i.e. "JulYTD") (Note: no space)

• MMMBOY - Balance of Year, exclusive of the month referenced (i.e. "JulBOY" for Aug through Dec)

• MMMTTM - Trailing 12 Months (i.e. "JulTTM") for Jul of current year through Aug of prior year

Year - a four digit year

Version - the version of the data to pull. Available versions include:

• Actual (from hotel P&Ls)

• Budget (current/final budget)

• Budget1 through Budget12 (budget drafts)

• Forecast1 through Forecast12 - monthly forecasts as of the end of the month number (i.e. Forecast6 is the forecast received at the end of June/with June actuals)

• Proforma

• LY_Actual (Last Year Actuals, same Month)

• Var_LY_Actual (Variance to Last Year Actuals)

• Var_Budget (Variance to Budget)

Example:

=SP.FINANCIALS_AGG("XYZ,ABC,DEF" ,"Total Revenue - 100","JulYTD",2023,"Actual")

Will return the Room Revenue for the sum of hotels XYZ, ABC, DEF for July Year-to-Date 2023 Actuals.

NOTE: When combining with SP.FILTER(), the best performance will be achieved when using SP.FILTER once on the worksheet in a single empty column to return the list of codes, and then referencing the column range as the property code input to the SP.FINANCIALS_AGG() functions used in a sheet. This prevents excel from having to calculate the filtered hotel list for every cell and instead calculates the list only once and then passes that to all other cells that are pulling data.