XLGL 4.0 Preview

We just added a video preview of XLGL 4.0 on YouTube. The new version won’t be available for a while, but you can still let us know what you think. (Watch it on YouTube)

XLGL 3.3

We just released XLGL 3.3 with new material in the Getting Started guide and 3 new templates. There are other minor addition, but today I’ll focus on the new templates.

In the past couple of months, we have been getting more and more question on Excel PivotTables and Dashboards. Here are quick answers: Yes, XLGL works with PivotTables, and Yes, XLGL can produce amazing Dashboards.

Because dashboards are usually very personnalized, we never included any model/template for them. XLGL 3.3 changes that. We’ve included 2 new templates based on PivotTables and one complete company dashboard, featuring all sort of Excel tools that XLGL can work with: Shapes, Autofilters, Tables, PivotTables, Graphs, …

These templates can be found under the XLGL Ribbon/menu, and Templates. They were built specifically for Excel 2007, but can be used with previous version of Excel if you have the Microsoft Office Compatibility Pack (Available here). They just look better in Excel 2007.

Here are some snapshot:

We won’t be adding any new templates until XLGL 4.0. If you have any special request, make sure to let us know.

Pierre Alain

XLGL 3.2

Last week, we released version 3.2 of XLGL. If you have opt to receive notification on startup, XLGL will have told you so when opening Excel. If not, you can go to XLGL, Check for Updates.

As we’re deep into development for version 4.0, our biggest update ever (available next fall), we found that the following improvements would make a difference to a lot of people and decided to include them in the current version.

So, what’s new? Small improvement that will make, we hope, a big difference. The first thing you’ll notice, if you’re using Excel 2007, is the new Ribbon interface:

 

 You still have access to the XLGL menu under Add-ins, but we believe this integrated interface will serve everyone better.

The next thing you might already have noticed on the image above is the Templates button (also available in the regular menu). All reports that were previously online are now available directly from XLGL. Just click Templates, pick your report and click OK. Moreover, we’ve improve all the report for a common look and feel.

Finally, we’ve replaced the tutorial with a Getting Started Guide which covers more information and shows a greater diversity of what can be done, without going to far into technical details. We hope new users will appreciate this one.

Other than that, there are some more minor improvements, such as the Suggest button on the Ribbon to make a quick suggestion, and other minor fixes.

Let us know what you think. Anything else you like to see added quickly?

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.

Looking back on the survey

First, I want to thank everyone who filled out the XLGL survey. Your input is greatly appreciated. If you have not filled the survey and would like to do so, you can do it at this address:

http://survey.constantcontact.com/survey/a07e2n55ajzg2hoscu8/start

Getting the good comments on what is working well is always nice, but getting your input on what is missing/broken/making you crazy is what we are really looking for; and we got a lot of it. I’ll probably need a few posts to address some of the things that can be addressed now.

Some statistics:

• 80% of respondents are using Simply Accounting 2008 and up;
• 70% of respondents are using Excel 2007;
• 50% of respondents consider themselves beginners with XLGL;
• 70% of respondents are likely to continue updating XLGL, but want to see more new features.

Other key points:

• Ease of use. There are 2 clear groups here. Those that think that XLGL is a breeze to use and just want more features, and those who are still having a hard time using it.
• Documentation. Incomplete, all over the place, hard on the new user.
• Customer service. Above average. Thank you.
• More, more, more. More features, more templates, more training.

Some of the suggestions made are being implemented right now. Others will come later in 2010, as a part of our big update to XLGL. While adding new feature, we are also hard at work to make XLGL more accessible to everyone, without regard to their Excel skills.

I will personally read all of the survey form and may even contact some of you. In the meantime, remember that we are always listening, so send your comment/suggestion this way.

Pierre Alain

Resolving Problems

With XLGL 3.0 out, I think we need to spend some time on resolving general issues with XLGL and listing resources available to you.

About 50% of all support call get can be resolved by updating your system. XLGL is bound to Excel and works on Windows. First, make sure you use Genuine Software and that you have the latest update and Service Packs for Windows and Excel.

Then, of course, having the latest version of XLGL really helps. We constantly make improvement ourselves and release updates as needed, free for anyone with a valid subscription to support. These updates may add new features, but also solve bug and add support for changes made either in Excel or Simply Accounting.

You can look at your current version from Excel, under XLGL > About… You can also run XLGL > Check for Updates to see if an update is available and download it.

What if that doesn’t solve it? When XLGL generates an exception, you can send the error report to us, with your own comment. This is anonymous, so if you want to be reached back, please include your email.

Use XLGL > Contact Support to send us support emails. This will include technical information such as the version of XLGL and version Excel you are using and can help us resolve the issue faster. Emails can include screenshot, workbook, Simply Accounting backup file or any other file you deemed necessary. Too much information is better than not enough.

support@logicimtech.com is the email you want to use if you do not have access to XLGL for some reason.

The help file included with XLGL can also help you solve problem, while learning more about XLGL.

This blog along with the french one continuously provide up-to-date information and solution for XLGL.

Look at Twitter (here in French) for the latest news and alerts.

Check out our web site for examples, tutorials and other information.

Did I forget anything?

Pierre Alain

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.

Registering XLGL in Excel

For some reason, either after installation or later on, the XLGL menu may not appear in Excel. Actually, the add-in is not loaded at all and it becomes impossible to refresh your report. This can be caused by updates, antivirus/antispyware program and others.

Luckily, fixing this is easy. Here are the steps to take in Excel 2000-2003 and then for Excel 2007.

Excel 2000-2003

1-On the Excel menu, click on Tools, then Add-ins.

2-Mark the checkbox for XLGL and click OK.

Excel 2007

1-Click the Office button. (the big round button in the upper left)

2-Click Excel Options (at the bottom right of the window)

3-Select Add-ins in the left panel.

4-At the bottom of the window, click Go…

5-Mark the checkbox for XLGL and click OK.

In the case where XLGL would not be in the list, click on Browse… and retrieve the XLGL.XLL file from the installation folder. If you did not change it at installation, it will be:

C:\Program Files\Logicim inc.\XLGL\XLGL.XLL