XLGL Installation

With version 4.3 of XLGL, installation has been simplified and corrected in many respect. In this article I want to covert some of the changes and special issues when upgrading.

1. Uninstall previous version. This is very important. You must uninstall the previous version of XLGL before installing a new one. This can be done using the Windows Control Panel or with the Start menu (XLGL | Uninstall XLGL). This is necessary because Windows does not do a good job of replacing files for add-ins.

2. Each user must install XLGL in his/her account. XLGL does not require administrative right for installation anymore and user must install XLGL in their own account. This is necessary because the Excel XLL add-in can only be register per-user.

• Users do not need any special permission to install
• The XLGL add-in will only be register in the Windows account where it is installed
• Licensing is still done by computer. Any number of users can install XLGL on a computer and still use a single license
• Do not try to run the installation package ‘As Administrator’. This will install in the Administrator account.

3. By default, the installation folder is in the user account. There is no reason to change this, but if you do, you should still install in a location that only the current user can access. (not Program files)

4.Registration of the add-in in Excel is more solid than before. If it fails for any reason, you can force a registration from the Windows Start menu (XLGL | Register XLGL).

If this still fails, you can send files under My Documents\Add-in Express to support@logicimtech.com.

This new installation frees users  to install the software without administrator, and frees the concern for administrator of installing malicious software. It also enable user to have different version of XLGL (beta version…) under different accounts.

Please let us know if you have any installation issue not covered in this article.

Pierre Alain

XLGL Toolbox #1

Hidings rows with zeros

Drilldown on reports

Use Freeze to Create Static Reports

Updating XLGL 4.0 Reports for Simply Accounting

XLGL 4.0 Reports

XLGL 4.0 is here

XLGL 4.0 is finally here. The current version is actually 4.0.4 since we released some ajustment today.

In the upcoming weeks, we will be updating learning videos. The first one is already up:

Summer classes are also available, so you may want to check that out before all seats are taken:  http://www.logicimtech.com/xlglcourses.aspx

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.