Need more, need help now?
- - - - - - - - - - - - - - - - - - pay-as-you-go support - no contract - tenth of an hour billing - expert help - fast service - no call queues
Need integration?
- - - - - - - - - - - - - - - - - - with your shipping system - website - invoicing system - crm - cms - manufacturing - order import - back to back orders..
Need a report?
- - - - - - - - - - - - - - - - - - Excel reporting that pulls data from Sage - custom layouts - layouts that change adapt to your brands and/or for drop shipping.
Want web hosting?
- - - - - - - - - - - - - - - - - - Your own domain name - email - a shop - wordpress - woo commerce - ticket systems - help desks - forums - portals

Data Export Help

Sage 50 general help forum - Free help and support for all general issues
Post Reply
Nasserrabilaleh
User
User
Posts: 2
Joined: 18 Apr 2019, 11:10
Sage Version: US Sage 50 Version (Peachtree)

Data Export Help

Post by Nasserrabilaleh » 18 Apr 2019, 11:18

Hello All,

I am new to sage, but trying to build an excel sheet that from on data export from sage will populate:
P&L by division & consolidated view
Balance sheet consolidated view

I thought this would be simple, and i have achieved it, however oddly i cannot get the bank accounts to reconcile back. Every other nominal code agrees. (when looking into this it looks like on random transactions it thinks its a credit rather than debit??)

My current method was to:
Go to departments: Reports: Monthly Profit and Loss by Department: edit report: Drag in department field and Name: Change the criteria to not exclude any categories. And then do 'data to excel' and i can work on this source data. And this method has worked perfectly for everything other than the bank account.

Therefore i was wondering if there is a better way to do this. In terms of a data dump the important fields i need are:

All Nominal Codes
Department Number
Period (Period 0 being brought forward)(i only need to run this for a year at a time)
Amount
ChartOfAccountsSubCategory.RangeDescription



If anyone has any other suggestions of how i can do this, or had similar experiences with the bank not reconciling on a data download from department reports, i would really appreciate your help.

Many thanks,

Nasser

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4616
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Data Export Help

Post by brucedenney » 21 Apr 2019, 18:18

The trouble is you need to do that every month.

The way I would do that is to dump the entire audit header and splits into a spreadsheet using odbc and msquery.

This gives you the data you need for almost any analysis.

You could export the chart of accounts and build from that as well, or you could pull the nominal ledger then build columns across the sheet to give you a departmental TB or a TB for each of the last 12/24 months or whatever you need.

From there you can build a chart of accounts.

And to run it next month, you change the date and hit refresh or if you find a late entry to this month, just put it in Sage then hit refresh.

Periods do not exist in Sage 50, it is "open item", so you add a period value column and write a formula to be the net amount if in the date range specified.

If you didn't need the departments, you could do this off the nominal ledger "bucket" values for each period, using an offset to get the value for this period.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Nasserrabilaleh
User
User
Posts: 2
Joined: 18 Apr 2019, 11:10
Sage Version: US Sage 50 Version (Peachtree)

Re: Data Export Help

Post by Nasserrabilaleh » 23 Apr 2019, 15:47

Hello,

Thank you so much for your reply, it sounds positive, and although i understand what you are saying, im not sure of the method you are referring to.

Would you edit an existing report (if so which one) or go into file create and create a new report (If so what document type would you use)

I keep trying various things but the data coming out is less than usable.

Like i said in the first post, the best luck I've had is to amend the departmental P&L to include all nominal codes, and department fields. this works for every department and every code, apart from bank (I'm not sure if this is an known issue)

Unfortunately when contacting Sage themselves they wouldn't recommend editing any reports and couldn't advise me any further other than trying to sell an excel report creator.

If you could provide a little more detail i would be incredibly great full.


(in terms of having to do a monthly download this is what i'm aiming for -
The current process: For each department (we have around 25) we use the excel report downloader and update the P&L for each one. and then the B&L.
I'm trying to create a new process to do one download a month to update all P&L & balance sheets automatically. A time saver, and means the format is much better.

I have made this work with the method above, however the only codes that do not reconcile are the bank nominal codes. as it randomonly switches the signage on some transactions (which i have managed to identify)

Thanks,

User avatar
brucedenney
Site Admin
Site Admin
Posts: 4616
Joined: 28 Feb 2006, 09:56
Sage Version: v28 UK/Europe/Africa

Re: Data Export Help

Post by brucedenney » 25 Apr 2019, 08:43

You do not do anything in Sage.

In Excel you "get data" from odbc, select the sage driver and launch msquery, this then lets you select the tables, link them and select the fields you want, this creates a refresh-able link to the data in sage.

To do departmental reporting you essentially pull the audit header and split information from sage into an excel spreadsheet (think of the transactions screen as a spreadsheet), from there you can use a number of techniques to consolidate, sum and analyse the data.
For just about anything Sage :- Discount subscriptions, pay-as-you-go support, application integration, reports, layouts, linked excel spreadsheets, analysis or any other help making life with sage easier/less time consuming Contact me.

Post Reply

Who is online

Users browsing this forum: No registered users and 12 guests