AI Tips & Tricks | Set Up Expense Categories with AI

The Financial Dashboard on the StrataMax Portal shows owners a donut chart of their building's expenses by category. The categories come from the Expense Summary ranges set up in StrataMax. If no categories are set up, the chart shows the 15 largest expense codes instead.

Setting up ranges by hand for a large portfolio takes time. Instead, you can export your expense codes, use your organisation's approved AI tool to suggest categories and build the ranges, then import them in one step.

Before You Start

• You need StrataMax 5.6.165 or later.
• Use your organisation's approved AI tool. The export holds building numbers and names, account codes and descriptions. It holds no owner details.
• The import replaces existing ranges, and it can't be undone. Record your current setup first (section 1 below).
• Global Ranges apply to every building. If a building has Building Override Ranges, they replace the Global Ranges for that building completely, so a building's override ranges must cover all of its expense codes. The prompts in this article build a complete set of override ranges for each building.

1. Record Your Current Setup

1. Search or select Financial Statements.
2. Go to Options > Edit Account Summary List and choose Expense Summary in the drop-down.
3. Take a screenshot of the Global Ranges and of any Building Override Ranges you have set up.

2. Export Your Expense Codes

1. Search or select Search Global Accounts.
2. On the Restrict To tab, change the last row to Account Type, is equal to, Expense.
   
   
   
   
3. On the Advanced tab, set Maximum Number of Records to Retrieve to Unlimited.
   
   
   
   
4. Click Refresh. Check the number of records displayed under the grid. If it reads exactly 1,000, the limit is still set.
   
   
   
   
5. Click Export and save the workbook.

3. Ask the AI to Suggest Categories

1. Open your organisation's approved AI tool and attach the exported workbook.
2. Copy Prompt A (below), paste it in and send it.
3. Check that the number of rows it says it read matches your export.
4. Download the workbook it returns.

4. Review the Categories

Open the workbook and go to the Category Map sheet. It has one line for each account code and description, not one for each building. Review the below then save the workbook.

• Change any category you disagree with on this sheet.
• Leave a category blank if it doesn't fit anywhere. Blank codes show as Other on the dashboard.
• Keep category names to 30 characters or fewer.
• Look at the Note column. It explains where the AI was unsure.
• Check the codes in your capital works and sinking funds (codes starting with 2 or higher). Tax and GST codes there are often put in Capital Works by mistake.

5. Ask the AI to Build the Import File

1. Start a new chat, attach the reviewed workbook, paste Prompt B and send it.
2. Download both files it returns: Expense Summary Import.xlsx and Range Check.xlsx.
3. Read Range Check and deal with anything it lists for review.

6. Check the Import File in Excel

Open Expense Summary Import.xlsx. If Excel asks, click Enable Editing. Check that:

• there is only one sheet;
• row 1 holds exactly these headers: Building Number, RangeCategory, CategoryName, RangeStart, RangeFinish;
• every row says Expense Summary in RangeCategory;
• building numbers keep any leading zeros, for example 011025.

If the import closes Financial Statements, open the file in Excel, save it and import again.

7. Import the Ranges

1. In Financial Statements, go to Options > Edit Account Summary List and choose Expense Summary.
2. Click the import icon at the top right.
   
   
   
   
3. Select the file and click Open.
4. A warning explains that the import replaces the existing ranges for each building in the file. Click Yes.
5. Wait for Import successful and click OK. Large portfolios can take a minute or more. The ranges are saved straight away.
   
   
   
   

8. Check the Result

Choose two or three buildings. Open the Edit Account Summary List for each and compare the ranges with the file. Once the Financial Dashboard is live for a building, check its expense chart.

Prompts

Select all the text in a box and copy it (Ctrl+C), then paste it into your AI tool. Paste the whole prompt rather than retyping it.

Prompt A: Categories

I've attached an export of expense account codes from StrataMax (Search Global Accounts). It lists Building Number, Account Code and Description for every expense account in every building we manage. I want to group these codes into categories for the expense donut chart on our owners' Financial Dashboard.

Please:
1. Read the whole file with code, treating every column as text. Keep leading zeros and don't change any existing values. Tell me how many rows you read and how many unique Account Code and Description pairs there are.
2. Work from the unique Account Code and Description pairs, not row by row, so the same code and description gets the same category in every building.
3. Give each pair one category that an owner would recognise from strata or body corporate financial statements. Use no more than 12 categories, and keep each category name to 30 characters or fewer.
4. Don't create an "Other" category. If a code doesn't clearly belong anywhere, leave its category blank. The dashboard shows anything without a category as Other.
5. Codes starting with 2 or higher belong to capital works, sinking and other funds. Give them their own categories, such as Capital Works, rather than mixing them into admin fund categories.
6. Return an .xlsx workbook with two sheets:
   - "Category Map": one row per unique Account Code and Description, with the columns Account Code, Description, Category, Number Of Buildings and Note (say why for anything you were unsure about).
   - "Accounts": the original rows with a Category column added, filled from the Category Map.

Prompt B: Import File

The attached workbook has two sheets. "Category Map" has the reviewed category for each Account Code and Description. "Accounts" lists every Building Number and Account Code. Please use it to build a StrataMax import file of Expense Summary account code ranges, one set per building.

Read the whole workbook with code, treating every column as text.

Rules:
1. Account codes are text. Sort them as text, character by character, so a shorter code comes straight before longer codes that start with it: 143, 1435, 14350, 1436. Never compare them as numbers.
2. Take each account's category from "Category Map", matching on Account Code and Description. Accounts with a blank category get no range.
3. Work one building at a time and, within a building, one fund at a time. The fund is the first digit of the account code. A range never contains codes with different first digits.
4. Walk the building's sorted codes in order. Start a range at the first code of a category and keep extending it while the next code has the same category. Close it at the last code before a code with a different or blank category. The range runs from that first code to that last code. The same category can have several ranges.
5. Check every range: no two ranges in a building overlap, and every code in the building that sorts between a range's start and finish has that range's category.
6. If the same building and account code appears with two different categories, leave that code out and tell me which ones.

Give me back two files:
- "Expense Summary Import.xlsx" with ONE sheet and exactly these five column headers in row 1, in this order: Building Number, RangeCategory, CategoryName, RangeStart, RangeFinish. RangeCategory is the text Expense Summary on every row. CategoryName is the category. Store Building Number, RangeStart and RangeFinish as text so leading zeros stay. No title rows, totals, notes, formulas or other sheets. Write it with the xlsxwriter library if you have it.
- "Range Check.xlsx" listing each range with the number of account codes it covers, and anything I need to review.

Notes

• Importing again replaces everything for each building in the file. Always import a complete set for a building, not just the categories you changed.
• New buildings have no override ranges, so they use the Global Ranges until you import a set of ranges for them.
• New account codes added later take the category of the range they fall inside, or show as Other if they fall between ranges. Repeat the process when your chart of accounts changes.
• More than 15 categories: the dashboard shows the 15 largest and groups the rest as Other.
• CSV files can be imported too, but without a header row.

Troubleshooting