Share this content
0
720

How to extract accounting data into Excel?

Linking Excel to Sage or other accounting software

Didn't find your answer?

Search AccountingWEB

Hello,

I'm hoping to get some advice on tips to extract data from accouting software into Excel. So instead of continuously downloading the same report, I could refresh a table in Excel.

I'm aware that there are ODBC links, but I am struggling to find places where I can learn about these and how to apply them. If anyone has got any advice, it'd be greatly appreciated.

Thanks,

Alex

Replies

Please login or register to join the discussion.

avatar
By NHGlos
25th Mar 2019 13:35

Hello

A company I worked for several years ago used ODBC to extract data from Sage 50 into Excel, it was set up using a guide produced by the ICAEW - it may have been called something like "Automating Management Accounts".

Using ODBC to extract meaningful data requires the user to have some knowledge of the database in question, have you contacted Sage to see if they produce any guidance. Having used ODBC with much larger SQL databases, the flexibility in the extraction from Sage 50 (in terms of what is extracted, how it looks etc.) is relatively limited but I would say a little more user friendly.

Thanks (1)
By tom123
25th Mar 2019 14:33

ODBC is definitely the way to go - but you need to learn how it all works. Probably beyond the scope of questions on here.

Having said that, I could not do any of my work without ODBC - all of my reports and queries are developed this way - I have a library of about 100 set up on our ERP system.

Worth persevering with, and then you will never be printing out tables and sitting with highlighter pens again :)

Thanks (1)
avatar
25th Mar 2019 15:56

Please ignore the below, it is wrong...

[I think ODBC only works with Sage Line 50 (if it is still called that?)

Sage 200 has it's own way of doing things.

I'm happy to be told I'm wrong, though...]

Thanks (0)
26th Apr 2019 14:50

Hi

Sage 50 ODBC is a very powerful way of getting data access. However in large Sage 50 data sets or when an ‘auto refresh’ is required (e.g. to update prices on web site, latest stock positions on EPOS system, produce consolidated figures from multiple Sage 50 companies ..) then you may want to have a look at SAGE2SQL

https://www.red-it.co.uk/sage2sql

This transfers all the key Sage data into a Microsoft SQL database from where you can create views, web based reports or connect to Excel via ODBC or Microsoft Power BI for incredible data analysis / reporting.

As for Sage 200, this already uses a Microsoft SQL server to hold the data so ODBC access is again possible. It should be noted that Sage 200 data structures are a lot more complex and detailed knowledge of tables and joins will be needed to ensure the correct data has been extracted. Comparison to the ‘nearest’ standard Sage 200 report is highly recommended as it is only too easy to get the wrong data set out!

Please feel free to contact me if you have any further queries.

Ara Martirossian FCA
[email protected]

Thanks (0)
avatar
26th Apr 2019 18:14

I run my own consultancy and this is a large part of my ongoing work. Get in touch on [email protected] if you would like more help and advice

Nick Ashdown
Ashdown Consulting Ltd
Www.acl-solutions.co.uk

Thanks (0)
Share this content