More Simply Accounting Reports using XLGL 4.0

Last month, we uploaded a preview of XLGL 4.0 with a general outlook on the entire program.

Today, I’d like to share this new video which focuses on the reports included in XLGL 4.0. Starting with version 3.2, we added report templates directly in the program, a well receive feature.

With XLGL 4.0, we continue on that path, adding many more reports and making it easier than ever to find what your looking for and to customize it any way you want. (Watch the video on YouTube)



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



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)

News on XLGL 4.0

In the upcoming weeks, we’ll be releasing preview bit of XLGL 4.0, our next version of Simply Accounting Reporting.

Alan Salmon will be showing a short video during his Accounting Technology Seminar and we’ll have an in-depth look at the new features at the Simply Accounting Partner Conference in 2 weeks.

Stay tuned for more.

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 on Facebook

XLGL now has its own page on Facebook.

Become a fan now!

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

Connecting to your Company Data

The very first thing anyone must do when using XLGL is to connect to a Simply Accounting Company. From time to time, we receive calls (and emails) from people who are unable to connect. In this article, we’ll look at the reasons you may be unable to connect to your Company Data.

The single most probable reason is that your user does not have Third Party Access right. By default, Simply Accounting users do not have the right to access the data from outside of the program. This includes sysadmin.

To assign Third Party Access right:

1. Open your Company Data in Simply Accounting;
2. Go to Setup, Set Up Users & Roles;
3. Enter the password for sysadmin and click OK;
4. Select the user from the list and click Modify User;
5. Under Rights With Third-Party Products, click Read-only access or Read/write access.
6. Click OK.

XLGL only requires Read access, but if you need to use other third-party, you may need to assign Read/write.

The second most encountered problem has to do with the user name. When accessing Simply Accounting, if you have a user called John, it doesn’t make a difference if you use John or john. The user name is not case-sensitive. However, when connecting from outside of Simply Accounting, this is not true anymore. If the user was created as John, you must use John and not john. A good practice is to have all user name in lower case.

Then, of course, there is the wrong password issue. Make sure you got your password correct. Same thing goes with the file. Sometime people make copies of their database, or change the name when they start a new year. Make sure you’re accessing the correct file.

Here’s a quick check list to finish:

1. Make sure you can open the data with Simply Accounting on the same computer where you are using XLGL;
2. Make sure you are opening the correct file;
3. Make sure you have the correct user name and that it’s spelled correctly;
4. Make sure the user has Third Party Access rights;
5. Make sure you have the correct password. Changing you password to something simple (such as 1) can help you find if this is the problem or not.

Did you face a different situation? Please, let us know.

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.