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.

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.

Financial Reporting #1

When doing financial reporting, you usually want to report some monetary figures from the General Ledger (GL) over a period or at a certain date.

Using traditional and general purpose reporting tools such as Crystal Report or Ms Query, you would need to create a custom query using multiple tables. This can become complex rapidly even when you’re not using Departments or projects.

Although XLGL has custom querying tools as well, it offers a nice alternative, both easier to use and more flexible: the worksheet function.

An XLGL worksheet function is a regular Excel worksheet function. It takes parameters, does some calculation and return a result in a cell. The only difference is that the XLGL function will fetch data from your Simply Accounting file as needed to perform its calculation.

For example, entering the following formula in a cell:

=XGLP(4010, “August”)

will return the amount of all transaction posted to account 4010 in August of the current fiscal year. Of course, the account number and the period can reside in other cell that you reference in your function:

=XGLP($A4,$ C$1)

Although you can learn the function and parameter name, XLGL offers a nice drag and drop interface integrated with Excel. You can drag a function, drop it in a cell, set up the parameter using an easy Wizard and then copy the formula to other part of the worksheet.

 

The following online videos shows how to build a complete Income Statement using this method:

http://www.logicimtech.com/learnxlgl.aspx

Here are some more example of how the XGLP function can be used:

=XGLP(“Revenue”, “August”, “December”, “0100″)

will return the total amount for all revenue accounts using department 0100 for transaction made between August and December.

=XGLP(“5010, 5020″, “First Quarter”,,,”ACME Novelty Ltd.”)

will return all purchase made at ACME Novelty Ltd. in account 5010 and 5020 in the first quarter of the current fiscal year.

Of course, the power of the worksheet function is best shown not in its individual use, but when use to create a full report (see video above). Once you have report finished in this way, you can just change the period to update everything very rapidly.

Next time, we’ll continue our financial reporting tour with another function use to get the balance of an account.

Is there some type of financial reporting you think would be difficult to do using this method? Let us know!

What is XLGL?

So what is XLGL and, more importantly, what can it do for you?

Technically speaking, XLGL is an Excel Add-in, a piece of software that attach itself to Excel and augment its features. XLGL creates a bridge between Excel and Simply Accounting both easier to use and more powerful than MS Query or other import/export tools.

In more simple terms, XLGL helps you report from Simply Accounting data using Microsoft Excel.

Now, most people I’ve talk to in the accounting world use Excel for reporting, analysis, forecasting and other words I don’t quite understand At some point you may need data coming from Simply Accounting. The balance of an account, a list of projects, a phone number?

If you’re creating financial statement, you may need a lot of data from Simply Accounting, and you’ll want to organize it in a certain way. Same thing for an inventory report, or a sales report. Maybe you just need your total sales for the period? Whatever it is, XLGL will get it for you, with the help of easy to use functions and lists, that are both flexible and powerful.

Since most people use XLGL for financial reporting, in my next blog entry I will take some time to show how to report those number using XLGL. One simple function.

How do you do your reporting using Excel? Do you enter everything by hand? Copy and paste? Import? Let us know.