Drilldown on reports

Use Freeze to Create Static Reports

Updating XLGL 4.0 Reports for Simply Accounting

2010 Simply Accounting Partnership Conference

We just spend the end of last week at the 2010 Simply Accounting Partnership Conference. What a blast! I’ll pass over the cowboys and dancing on the table, and move straigh to XLGL.

For the first time, we showed XLGL 4.0 to a live audience. Feedback was really encouraging; seems we’re on the right track. (a preview of XLGL 4.0 is available on YouTube)

The most popular improvement was, by far, the list formatting. In XLGL 3.0, you have to format list using specific Excel styles or you lose all formatting the next time you refresh. Very frustrating.

Not only is this not a problem anymore in version 4.0, but XLGL also looks at those changes and apply them all over the layout for a consistent look.

Next in line was the Freezing feature, which is now applied to the entire workbook by default and does not remove your formulas, only XLGL specific formulas.

As for new features, the ability to navigate the entire company data using a simple drag and drop interface blew everyone away. For some, when I showed how to build a complete Pivot Table with a few click, starting with nothing, it was all they needed to see to believe how XLGL is innovative compared to other products.

Not far behind was the new parameter autofill feature for worksheet function. That one is still in development, but it’s sure to be a very popular feature. Or not, as most people won’t even know it’s there while fully enjoying its benefits.

No, we did not show everything. There are still some surprise. We will continue our releases of preview videos until the ship date.

Thanks to everyone who made XLGL such a success, and thank you for your patience. XLGL 4.0 will be available soon.

Pierre Alain



Getting Total of all Receipt per Customer

While reviewing the survey responses we got last month, I found an interesting question/comment on getting the total of customer receipts for the year. This is one example of the type of flexibility you have with XLGL. Let’s look at the solution.

Basically, what we want is a list of customers with an amount next to each name showing how much money we received for a given period. For this example, we’ll suppose we have the beginning and ending date for our period in B2 and B3.

Getting the list of customer is quite easy. We open the XLGL Console and go to Receivables and Clients List. We  drag and drop the word ALL to cell A5. This is what we have:

 

Now come the real challenge. We want to know how much money we receive from these customers. Money received goes to the bank, so let use our GL function and get the amount of money that entered the bank account from each customer.

Still using the XLGL console, we go to General and Worksheet Functions. We then drag and drop Amount of GL transaction for period to cell B5 and fill out the Function Argument window like this:

The parameters are filled like this:

• Accounts: 10600 >> This is our bank account. We could have put this in a cell and reference to it as well.
• Beginning and End: the date in B2 and B3 on our worksheet.
• Customers: A5 >> The first customer in our list.

Note that you have to scroll down to see the Customers parameter as shown in the second image above.

We click OK (or press Enter) and Fill down/copy our formula next to each customer to have the final result.

  

For the more technically inclined, the formula bar is showing this:

 

Change the dates in B2 and B3 to switch period and watch the amount update.

You can drill down on an amount by right-clicking on it and selecting Drilldown.

This is just one example of the flexibility of XLGL. Would you like to see another one? Tell us what you’d like.

Pierre Alain

Recalculate or Refresh

Very frequently, we receive questions about the difference between Recalculate and Refresh on the XLGL menu. Recently, someone asked the difference between the Excel recalculation shortcut (F9 and such) and XLGL Recalculate.

The quick answer is:

• Recalculate will update all formulas in the workbook, while making sure XLGL worksheet functions return the latest data from Simply Accounting;
• Refresh will update XLGL lists, adding/removing rows/columns as needed, and then recalculate everything.

Looking at the basic example of the Balance Sheet, Recalculate will update the balances, while Refresh will add new accounts to the list.

XLGL builds on the Excel calculation engine to provide flexibility and interactivity while keeping your report fully dynamic. There are two risks with this approach that the architecture of XLGL makes sure to avoid.

First, it could get very slow as each function act as a single query to the database. If a function takes 0.1 seconds to update because of network speed, you wouldn’t notice when you enter just one, but when you copy that over a column a hundred times, it would take 10 seconds to update.

Second, if the database changes while creating your report, you could end up with an unbalanced report. For example, imagine you create the assets part of a balance sheet. In the meantime, a transaction in Simply Accounting is made affecting both assets and liabilities. When you finish your report, you could have a report that does not balance.

XLGL avoids these problems with an architecture that buffers some part of the database. The upside is that only data needed to operate is fetched from the database into memory, resulting in very fast access and guaranteed to balance. The downside is that very recent transaction may not be available. This is very similar to the way Google operates.

So, what do you do to make sure you have the latest data from the database? This is where Recalculate and Refresh comes in. Using Excel’s recalculation shortcuts (F9) will only return the same data because the XLGL buffers are not refreshed. However, using XLGL Recalculate will refresh those buffers and then recalculate everything in the workbook, making sure everything still balance.

Now, data like account’s balance fit very nicely in a cell and can we can use the Excel calculation engine to update those easily. However, when the need come to enumerate data, such as a list of customers or transactions, then we need something else. This is why XLGL introduce Lists, an object similar to Excel’s QueryTable, but more powerful and adapted to our needs.

XLGL Lists, Excel QueryTables and PivotTables all have something in common. They will alter the worksheet by adding/removing rows and formatting. This is why Refresh functions are always kept separated from the calculation.

So, what should you use? Most of the time, you should be using Recalculate. Refresh is necessary when you have data that could add/remove rows/columns from your worksheet.

Refresh has giving some problems to people who lost their formatting and did not understand why. We are working on making this more user friendly. For now, just remember that Refresh can alter your workbook. If you’re unsure, save a copy of your work and test it.

XData and Family

Today, I’d like to introduce the new XData family of functions. Although these functions will be perfected and used later in higher level tools, they can be put to profit right now by advanced user to fetch any data from the database.

XData, XDataLookup and XDataCondition were introduced in version 2.9.9 of XLGL. XData is used to fetch data from database table. For example:

=XData("tCustomr", "sName", 1)

will return the name of the first customer in the database. The field name can be replaced by expression, such as:

=XData("tCustomr", "SUBSTRING(sName, 1, 5)+'.'", 1)

will return the first 5 characters of the first customer name followed by a dot (.). Aggregate functions can also be used. For example:

=XData("tCustomr", "COUNT(sName)")

will return the number of customer in the table.

You may have notice that the third argument is the index in the table of the record we want. Most of the time, you don’t know the index of the row you want. This is where XDataLookup and XDataCondition comes in.

=XDataLookup("tCustomr", "sName", "Ashburton Reinforcing")

will return the index of the customer named Ashburton Reinforcing, which can then be used in the XData function to retrieve other information on that customer:

=XData("tCustomr", "sCity", XDataLookup("tCustomr", "sName", "Ashburton Reinforcing"))

Of course, you can put both function in difference cells and reference them.

Sometime XDataLookup will return more than one index. For example:

=XDataLookup("tCustomr", "sCity", "Richmond")

will return the index of all the customer whose reside in Richmond. You can then get a count of these by using:

=XData("tCustomr", "COUNT(sName)", XDataLookup("tCustomr", "sCity", "Richmond"))

XDataCondition returns indices in the same manner but using any custom condition. For example:

=XDataCondition("tCustomr", "sName LIKE 'A*'")

will return the indices of all all customer that have a name starting with A.

In an upcoming post, we’ll look at how to use the XData family of function to create your own powerful worksheet functions.

XLGL and VBA

A question we are receiving more and more lately is: Can XLGL functions be called from VBA (Visual Basic for Application). The answer is Yes.

This is an advanced topic and, if you’re not familiar with VBA, you may be tempted to skip it. However, knowing a little VBA can save you tons of work on repetitive tasks. Starting with this post, we’ll talk more about XLGL and VBA, particularly the ability to create your own Worksheet Functions.

Although XLGL is not fully implement in VBA, it is still possible to call any functions by using Application.Run. For example:

Amount = Application.Run("XGL", "1020")

will return in variable Amount, the current balance of Account 1020.

The first parameter to Application.Run is always the function’s name followed by any function parameter you provide.

Menu command can be called in the same fashion. For example, to recalculate the workbook using XLGL Recalculate command:

Application.Run "XLGL__Recalculate"

To Refresh:

Application.Run "XLGL__Refresh"

This should give you a good head start on using XLGL with VBA. In further posts, we’ll look at custom worksheet function and answer any question we receive on the subject.

Financial Reporting #3

This is the last post of this series on basics of financial reporting with XLGL. So far, we have seen how to import financial figure using the XGLP (P stands for period) and XGL (to fetch the balance at a given date). Today we’ll see how to get budgeted amounts from the GL.

The name of the worksheet function is XGLBudgets (I’m sure you guessed that one) and it works very much like XGLP. Here are some examples:

=XGLBudgets(4010, “August”)

will return the budget amount for account 4010 in August of the current fiscal year.

=XGLBudgets(“5010, 5020″, “First Quarter”, “Second Quarter”, “0100″)

will return the total budget amount for accounts 5010  and 5020, and department 0100,  between the beginning of the first quarter and the end of the second quarter of the current fiscal year.

The function can be created using the XLGL console or using the Excel Insert Function wizard as explained in Financial Reporting #1.

As you may know, Simply Accounting requires you to define a budgeting period for your company: Monthly, Bi-Monthly, Quarterly, etc. When reporting using XLGL, you do not have to restrict yourself to these period. For example, if your budget are set as Monthly, you can still use the following function:

=XGLBudgets(4010, “First Quarter”)

In this case, XLGL will simply add the budget for the first three month of the year and return the result.

What if your situation is the opposite? You are using Quarterly budgets in Simply Accounting and want to report Monthly, as in:

 =XGLBudgets(4010, “August”)

By default, XLGL will use linear interpolation to calculate the budget. In this case, XLGL will return the proportional equivalent for August. Note that this won’t be exactly a third of the budget because XLGL interpolate using daily calculation. As August as 31 days, it will have a bigger budget than September.

Beside linear interpolation, you can also decide to clamp up or down the returned value, that is return the complete amount either when the period is partly present or only when the period is completely included, or using cubic interpolation. Cubic interpolation will take into account budgets from previous and following period to calculate acceleration or deceleration and return a more precise figure.

In summary, the XGLBudgets function will cover all budgeting scenario easily. Although we did not talk about it, it will also cover project budgeting.

Do you have a budgeting problem? Would you like to know more about project budgeting? Let us know!

Financial reporting #2

Last time we saw that using the XGLP function we can fetch any figures from the GL related or not to other module. One thing that XGLP won’t do is return an account balance.

With XGLP, you can leave the beginning of the period empty and use only an end date to retrieve a pseudo-balance. It is not a real balance because it does not use starting balance. XLGL only adds transaction amount, not starting balance for account/department.

You can still use the method above to get the balance for a project, or a vendor, but when you need the exact balance for an account or account-department, you must use XGL.

XGL is very similar to XGLP, only with fewer arguments. Here is an example:

=XGL(1020, “12/31/2008″, “0100″)

will return the balance for account 1020, department 0100 on December 31st 2008. XGL take only one date as it returns a balance. XGL will take your starting balance and add transactions amount up to the date you request.

This is the function you would use to create a Balance sheet for example.

There is one thing concerning the GL we still have to look at and that is Budget. Next time, we’ll look at the new XGLBudgets function as it related to the GL.