Excel Balance Sheet from Sage via ODBC

I am having problems with my excel Balance Sheet with data taken from Sage via ODBC as cash and VAT balance are floating nominal accounts and appear twice in my transactions listings (once as an asset and once as a liability). Does anyone know how I can exclude one of them ?

 

Regards

 

Raymond

Comments

Balance Sheet via ODBC

jason.raikes | | Permalink

Raymond,

We market a product called DBSage which has a number of pre-built reports.  These are presented in Excel.  I clearly remember the issue of floating accounts (cash, VAT etc.) and we had to develop a specific query to handle this and overcome the issue you mention.

If you are interested, I can provide a demonstration of the product over the internet.  Please email me at jraikes@pendragonsystems.com.

 

You can view details of the product at http://www.pendragonsystems.com/sage.htm.

Regards,

 

Jason Raikes

HI Raymond

Optegra | | Permalink

HI Raymond

Good question - we solved this when developing OptegraMRP by using clever excel formulas.

Depends on what you are doing, are you taking the chart of accounts and matching the nominal ledger accounts to the categories? You may be able to remove of the categories if you are not fussed about presenting the item in debtors or creditors when appropriate.

Look at the criteria "FLAG_ASSET_LIABILITY" table on the ODBC connection and add some formula to omit floating accounts in one of the categories?

I am happy to take a look at your output and see if our formulas are of any help?

If you are happy to email me the output file I will take a look free of charge

jeremy@optegra-financial.co.uk

Thanks

Jeremy

 

PS - Or Feel free to download a free trial of OptegraMRP from our website which does all this and a 25 page / 8 section management accounts pack in less than 30 seconds.. Single company price is £195 so may be a cheaper and quicker option...?

 

John Stokdyk's picture

I think David Carter may have looked into this

John Stokdyk | | Permalink

I certainly remember him moaning about how double entries can mess up ODBC data imports. The nearest I could get was in this tutorial article on cleaning up Sage ODBC data, but you might want to skim through the other ODBC import tutorials and some of his other tutorials

I'm sure the answer is in there somewhere, but will also contact the man himself to find out if he can give a quicker answer.

Create a SQL statement for yourself ....

JC | | Permalink

In Johns link David talks about analysis-ready files which make reporting far easier. You can create these in M$ Access by creating your own SQL statement(s)

OR

  • Start Excel
  • Choose Data --> From Other Sources (to get odbc)
  • Select your tables & fields & follow wizard (criteria, sort etc)
  • Choose the amend query/sql prompt
  • Either amend the requirements in the Query Window or change the SQL until

From memory you need to look at the following Sage tables (may have changed) - can't really remember but this should give a hint

  • Audit_Header - single transaction summary
  • Audit_Journal - think this is the transaction detail - anyway holds Nominal_Code
  • Audit_Split -
  • Audit_Usage -
  • Nominal_Ledger - totals for each period
  • Category - gives account range for each item
  • Cat_Title - allocates category item to Balance sheet categories

Also Sage uses identifiers (i.e. PI=Purchase Invoice, PP=Purchase Payment, JC=Journal Credit ..... etc) against each transaction to indicate what they are

Just use a query to JOIN the various tables & watch out for inner/outer queries because they do different things

SQL is actually not that complicated and especially when used with a WYSIWYG designer such as Access where the results are instant & it is quite difficult to get it wrong

If you know a bit of SQL or can use M$ Access (Queries) then why purchase 3rd party products that essentially do the same job? if you use Access then 'link' & don't 'import' - that way the same queries can be used against different companies sage accounts

At one time when David's threads were fairly new I posted a lot of actual SQL to deal with some of the problems encountered - also there were discussions on what the various tables (above) held and their use within Sage. A few years ago, so you probably need to research

Flag Asset_Liability field is the answer

David Carter | | Permalink

Agree with Jeremy at Optegra.  You have to apply a filter

           CATEGORY.FLAG_ASSET_LIABILITY = 0

Also, specify a particular Chart of Accounts if you have more than one.

  

 

Add comment
Log in or register to post comments