There are numerous ways to get your Balance Sheet into Excel via a NetSuite saved search. This article shows how to sum your G/L transactions from the beginning of time through ending data ranges to allow you to produce a comparative balance sheet with multiple months.

If you need to create income statements refer to this article.

Housekeeping

Naming Conventions

While not required, we recommend you use the following naming conventions.

Prefix your search title with CEAN (short for CloudExtend Excel Analytics), add a pipe character (|) and then a description of your search.
Example: CEAN | Balance Sheet | 0-3 Months
Your search ID will become the name of the table in Excel that holds your search results. We recommend using an _ followed by your search name.
Example: _cean_bal_sheet

Building Your Saved Search

We recommend building two searches, one to export your G/L summaries, and the other a list of G/L accounts and joining them together in the Excel data model based on the internal ID of the G/L account. This will increase the efficiency of your search and will also set you up for future success in slicing/dicing or working in Power BI.

🖥️ If you're on a Mac (Excel Power Query is limited but Microsoft has said feature parity is expected in the future) then you will want to expose your desired G/L account fields (number, description, etc.) on the primary transaction search results and build your pivot table off that search rather than joining the tables later.

Transaction Saved Search (Required)

Start with a NetSuite transaction search with the following criteria and results.

Search Criteria

Filter	Description	Comments
Posting	= True
Account Type	any of Bank, Accounts Receivable, Other Current Asset, Fixed Assets, Other Asset, Accounts Payable, Credit Card, Other Current Liability, Long Term Liability, Equity

Search Results

Field	Summary Type	Formula	Comment
Account: Internal ID	Group	GLAccountID	Use Custom Label of GLAccountID Note this is a field from the linked Accounts table and can be found near the end of all fields in your drop down.
			If you're on a Mac or choose not to use Excel Power Query then add G/L account fields desired here (account number, name, etc.) here and group them. Otherwise ignore this.
Formula (Numeric)	Sum	{amount}	Use Custom Label of Current Month This formula sums all the balance sheet transactions through the current date.
Formula (Numeric)	Sum	case when {trandate} <= last_day(add_months({today},-1)) then {amount} else 0 end	Use Custom Label of CM-1 This formula sums all the balance sheet transactions through the end of the previous month.
Formula (Numeric)	Sum	case when {trandate} <= last_day(add_months({today},-2)) then {amount} else 0 end	Use Custom Label of CM-2 This formula sums all the balance sheet transactions through the end of the month 2 months ago
Comment			Note you can add more rows with additional months if desired.
Formula (Date)	Maximum	{today}	Use Custom Label of Report Date
Subsidiary	Group		Subsidiary, Department and Class are optional fields, Consider adding them (and others) to further slice your financials.
Department	Group		ditto above
Class	Group		ditto above

G/L Account Saved Search

This search will simply list your G/L account and will serve as a lookup table when creating our balance sheet. As noted earlier this search is optional but recommended. This will increase the efficiency of your search and will also set you up for future success when working in Power BI or slicing your data. It will also be used to lookup Account Names and Descriptions in the formatted Balance Sheet you build.

🖥️ | Should you choose to skip this step then you should add the results fields to the top of the Transaction Saved Search above and group them.

Search Criteria

Filter	Description	Comments
There is no filter required

Search Results

Field	Summary Type	Comment
InternalID		Use Custom Label of GLAccountID
Number		Use Custom Label of Account Number
Name
Account Type
Description

Add your search results to Excel with CloudExtend

Once you've saved your searches add them both to Excel with CloudExtend. If you need help with that process refer to this article.

Save your file to OneDrive for Business or SharePoint

By saving your Excel file to OneDrive for Business or SharePoint CloudExtend will be able to update your data should you choose to enable scheduling now or at a future date. We recommend that you treat this as a source file and connect to it via Excel Power Query from another workbook.

Why use a source file approach? Once your source file is created it only needs to be opened by you if you need to perform a manual refresh via CloudExtend. Scheduled jobs can run with no issues because the file is never in use by someone else.

Connect to your source file with Excel Power Query

If you're not familiar with Power Query it's Excel (and Power BI's) best-kept secret. There are plenty of resources on the web where you can learn more about it. We also created this brief article for you, to learn how to connect your file with Excel Power Query.

Transforming your data

The current search results need to be transformed by rotating a portion of the table that holds your GL Transactions to transform columns into rows and to calculate the correct periods. The beauty of this is that the steps will be remembered each time the data is refreshed. If you forgot something you can always open the Query Editor in Excel and modify your steps.

Select the Balance Sheet Query at the top left.
CTRL-Click in each of the 3 columns (Current Month, CM-1, and CM-2).
Right-click, and select Unpivot Columns.
Next, replace values in the Attribute Column so we can use these values to calculate the actual reporting period in a subsequent step.
1. Select the Attribute Column and replace "Current Month" with 0
2. Repeat the step and replace CM with an empty value
Change they Type of the Attribute column to Whole Number
Add a new Custom Column called Period. The Period is calculated by adding the Report date to the Attribute field using the formula:
Date.AddMonths([Report Date], [Attribute])
Change the data type of Period to Date

Load your data to the data model by selecting the drop down under Close & Load and select Close & Load To
1. Select Only Create Connection
Select Add this data to the Data Model

Build Your Pivot Table

The first step is to pivot your data. This may be good enough for internal consumption but will also be used to feed the more formatted balance sheet that will be created in a subsequent step.

Pivot Table

Because we added our two tables to the data model, we can connect them together by a common key field and then pivot the data model as if it was one table. It's like VLOOKUP on steroids.

The first step is to link the two files together by a common key field, in this case the internal ID of the G/L account which we named GLAccountID.
1. Click the Power Pivot menu in Excel, and then select "Manage Data Model".
  (If you're on a Mac you won't see this option and should instead add the related fields to your saved search as outlined in the search description above).
2. In the data model tab click Diagram View to show the 2 tables. You may need to move/expand them to see all the fields. Then, connect the two tables by clicking on GLAccountID in one and dragging the line to the second table and dropping it on top of the GLAccountID in the second table. Go ahead and close the data model tab.
Next, we'll build our pivot table by selecting Insert-Pivot Table-From Data Model. It's important that you choose From Data Model as this will include both tables. To see this option, select the arrow under Pivot Table.
This is where Power Query really shines. By joining the tables you've eliminated the need for VLOOKUPS and can now add data from both tables to your pivot table.
1. Add Account Type, Number, and Name to the rows.
2. Add Period to the columns
3. Add Value to the values field.
4. From here you can start to make your pivot table more readable by
  1. Changing it to tabular format (Design->Report Layout->Tabular)
  2. Moving the Account types so Asset Accounts are at the top and Liabilities and Equity at the bottom. This is best done by clicking on any Account Type field such as Bank and then choosing Pivot Table Analyze from the ribbon, and then selecting Collapse Field.
  3. Remove grand totals (and sub-totals if you'll be building this as a formatted Balance Sheet)
  4. Remove the + and - indicators on the grouped fields (on the Ribbon, under PivotTable Tools tab, click the Analyze tab. and then click the +/- Buttons command, to toggle the buttons on or off). Your sheet should look something like this.
  5. Change the format of the period fields, in this case cell D2, E2, and F2 to a custom format of mmm-yy.
5. Next, add some slicers and format them. In our example we've added slicers for Period, Department, and Account Type. To add slicers, click anywhere in the pivot table and select Insert->Slicer. from the Pivot Table Analyze tab.
6. If you're comfortable using a pivot table and don't need board ready formats, then you're done. Remember to put your source data file on a schedule or rerun the searches manually before updating the pivots. To update the pivots, choose Data-Refresh All and this will refresh data from your source file and refresh your pivots in just a second or so. You can also right click your queries, choose Properties, and put them on a schedule as well. If you do need board ready formats (that update in seconds) with calculations for retained earnings as well then read on.

Beyond Pivot Tables

Excel is a blank canvas, and you have unlimited flexibility to design your balance sheet. The steps below are meant to give you a head start on crafting a custom formatted re-usable balance sheet sourced from NetSuite that can be updated in seconds. You can add other metrics such as budgets and variances as needed.

Build your Formatted Balance Sheet

In our example we're going to build a balance sheet that shows balance sheet data for a 3-month period.

Prep your pivot table

Remove the Account Type and Account Name from the Pivot Table results to help make our formulas easier to write. The only field left in the row should be Account Number.

Create and Format your Balance Sheet

Insert a new worksheet in the same workbook as your pivot table and give the worksheet a name such as Balance Sheet.

Leave a little room on the top and sides of your worksheet and create a shell to hold your data.
1. Add the text "Account Number" in C4 and then "Account Name" in D4.
2. Add a known G/L account number in C5 (in our example we added 1000). We'll use this to test our formula in the next step.
3. Insert a formula field in cell F4 that will return the column header of the first month in the results of your pivot table. Format it as custom mmm-yy and drag it out to cell H2 (or further if you've added more months to your search results).
Build your formula in cell F5 to extract the value for the intersection of Period and account 1000 from your pivot table.
1. Note, you may need to force your entry of 1000 to text for the formula to work. If so, enter '1000.
2. The below formula works when F$4 is the 1st month (January) and $C5 is the 1st G/L Account Number (1000). Be sure to use the relative references so you can drag your formulas. The IFERROR is necessary so that you can substitute 0 for a value if it does not exist in the pivot table. Note you may need to substitute the names of your tables in the formula below.
  
  =IFERROR(GETPIVOTDATA("[Measures].[Sum of Value]",PivotBalance!$A$1,"[customsearch_cean_gl_accounts].[Account Number]","[customsearch_cean_gl_accounts].[Account Number].&["&$C8&"]","[customsearch_cean_bal_sheet].[Period]","[customsearch_cean_bal_sheet].[Period].&["&TEXT(F$4,"yyyy-mm-dd""T00:00:00""")&"]"),0)
  
  Here's a link to a resource on YouTube with more information on the GETPIVOTDATA command for tables based on the Excel data model which behaves differently than a normal GETPIVOTDATA formula.
3. Drag your formulas down and across as desired and add a few more account numbers and cross check your Pivot Table to ensure that the formula is working properly. Don't worry about total rows and formatting yet, we'll get there.
4. At this point your sheet should look something like this.
Now we need to look up the Account Name based on the Account Number. You could do a VLOOKUP against the pivot table using cell references but that's not going to be dynamic. Instead, we're going to load our G/L search results as a table (remember before we just created the connection) and use a simple XLOOKUP.
1. Open up your Queries, right click on your G/L account query, and then choose Load as a Table in a new Worksheet (keep it in the data model).
2. Now we've got a table with all our G/L accounts, and we just need to add a formula in our Balance Sheet to pull in the Account Name based on the Account Number.
3. Use the formula below to lookup your Account Name from your list of G/L accounts (assuming cell C5 is the first field under Account Number).
  =XLOOKUP(C5,customsearch_cean_gl_accounts_ex[Account Number],customsearch_cean_gl_accounts_ex[Name],"No Matching Account")
  
  Once you drag your formula down your account names will then update any time you add/change the G/L account number. If your formula is not resolving, then add a single quote to the Account Number (ie '1000) to force it as a text field.
Format your balance sheet
Now that you see how to add your accounts and formulas you can start moving things around and formatting them. These are just a few suggestions; you can probably do better.
1. Turn off gridlines (View->Uncheck Gridlines checkbox)
2. Add headers for each account section and shade the top-level accounts.
3. Bold and center your column headers (row 4, ie Account Number through Months)
4. Right align the Account Name values
5. Insert a row at the end of your sections and use formulas to sum them.
6. Insert top or bottom borders to delineate totals at the end of each section.
7. etc. etc.
Add your slicers by selecting them in your pivot table and then choosing "Cut" (not copy) and "Paste" them onto your formatted Balance Sheet worksheet.
1. Format your slicers and place them as desired.
2. Note, if your silcers are not updating then select a slicer and choose Report Connections from the menu and make sure you've connected it to your pivot table.
Add your company logo
Hide the column with your G/L account numbers.
Your final product should look something like this.

Keep Your Numbers Up to Date

You've built your model, and it looks great, but now you want to refresh your data.

Source data

Consider putting the searches in your source data file on a schedule so they run in the background and are continuously updated. If you prefer to manually update your data, you can open the source file, load CloudExtend, and select Refresh All.

Balance Sheet

Your Balance Sheet is connected to your source file. To refresh the data you have several options.

Choose Data-Refresh All.

Set your query to refresh when the file opens or very X number of minutes. Open Queries and Connections and right click on the Query and select Properties.

Retained Earnings?

An update to this article will include details on how to add net income and retained earnings to your results.

Create an A/R Aging Report using CloudExtend Excel Analytics for NetSuite

FAQ: Formulas breaks when CloudExtend Excel Analytics Update a table

FAQ: NetSuite Saved Searches

Financial Statements

Creating Income Statements from G/L Search