Reports to Excel

I have seen that there is a lot of good advice within this group as to how to use Sage effectively.

I'm not sure whether I could now ever be persuaded to like it again but I will keep an open mind. Different programs suit different people.

Anyway, I have hit a problem with version 2010. I know, I have 2011 on my desk waiting to be installed, but at present I'm battling with 2010 because I haven't yet felt brave enough to try yet another upgrade.

My problem:  I needed a report in Excel. I needed a list of bank reconciled entries in date order, in Excel. Should be simple surely?

I ran my tried and tested csv run, as I have always done. But now the debit entries to the bank account (credits in this report) do not export into the same columns as the credits. Oh no! So much moving of cells had to be done. I ended up with a .pdf copy on the screen as well as the .csv copy, checking line by line for the problems. What has gone wrong?

Before I ran the .csv filel I did checkwhat could be the simplest method - 2010 is a relatively new version to me - what hasn't worked in the past might work now?  But no, as far as I can see it, the Sage run report to Excel is still next to useless because it doesn't put the basic data into the right columns and puts in loads of irrelevant title rows that need deleting and lots of cells are merged all over the place.

All I wanted was a simple transfer of one report into Excel so I could try to pinpoint the date when my client has clicked off items as reconciled when actually they are not reconciled. It took me almost two hours of playing about to get that report!!! So where am I going wrong? Is there a solution to this?

 

 

Comments

Sage reporting

jason.raikes | | Permalink

Anne Marie,

I think I may be able to help with your problem using our DBSage reporting tool.  If you would care to give me a call on 07776 307 037 to discuss the problem and see if we can sort it out.

Regards,

Jason Raikes

www.pendragonserver.com

CSV Files

johndon68 | | Permalink

The problem is that a lot of the reports in Sage, particularly those that have totals/expressions, don't lend themselves  to being saved as a CSV file where, typically, you'll have rows of information that are all identical.

I've found that saving the report as a Text file instead and loading that into Excel generally gives better results.

John

CSV files

janesorganic | | Permalink

I don't have version 10 but I am wondering which is your tried and tested report. There is often more than one way to skin a cat.

For example, if it were me I would create my csv file using nominal activity report rather than any bank report. I can't see a reconciled date but the flag is there OK.

I use Sage csv files quite a bit, and my solution to silly columns and so on is to set up a template in Excel that hides all the columns I don't want; run the csv, copy paste into the template and save the template as an Excel file with new name. (I work in a charity and receive lots of csv donation files that I work with in this way).

Setting up an Excel template means you can pre-set sort instructions, and macros if you're so inclined. It saves all that moving and deleting too. I also often bold the data to check and un-bold it as a form of tick.

 

I hope you find a fix!

Anne Marie Sewell's picture

Export to Excel

Anne Marie Sewell | | Permalink

Thank you all for your replies and some good advice, which I shall try.

It's so easy to get the entries into Excel from QuickBooks and so hard to get the same result from Sage. It gets quite frustrating, but I'll keep trying!

 

Import to Quickbooks

Briar | | Permalink

It might be easy getting Excel Reports from Quickbooks (but you get lots of annoying blank columns!), but you cannot import data into Quickbooks without expensive add-ons. Whereas it's very easy to import a csv file into Sage.

Have you thought about what would happen if your data in Quickbooks got corrupted but you had it in an Excel spreadsheet so wanted to import the correct date back in? You cannot do it! Whereas, it's dead easy in Sage. That's my reason for not using QB. It happened to a client and we could not reconstruct the accounts easily using Excel (converted to csv) and the backups of QB were corrupted!. I was able to dowload the list of transactions into Excel but could not import them into a newly installed "clean" version of QB. QB quoted a ridiculous price for doing it so the client stopped using QB (and I said goodbye to QB).

Where my clients use Sage I can prepare loads of adjustments (e.g. accruals and prepayments and reversal thereof, provisions for tax, dividends, etc.) in csv format and simply import them.

petersaxton's picture

Shouldn't be a problem

petersaxton | | Permalink

The debits and credits - if in the same column - need to be different sign so I just use copy and paste with value to get the negatives and then put them in the same column.

I do a sort first so all the debits are together.

It doesn't take long at all.

Pivot

Optegra | | Permalink

Dear Anne-Marie

Optegra Pivot will do this for you in under 30 seconds - all done in Excel (xls) format so no rubbish csv's..!

I will email you a copy free of charge for you to see how.

Can you send me your email address to jeremy@optegra-financial.co.uk

Thanks

 

Jeremy

Financials module

andrew wallace | | Permalink

Where I have to export (nominal) reports out of sage and the layout isn't that great, I go into the Financials module, put a filter on it and get the data out that way. Takes a bit of getting used to in the begining but is quite a powerful tool

sisko1's picture

Bank Transactions

sisko1 | | Permalink

Hi Anne Marie

Your best way is definately to understand how to extract data from the Sage database into Excel by using a process called ODBC. It is a little complex to understand to start with, but once you have the general idea it makes getting information out of Sage a lot easier. The particular report you want could be extracted in a few seconds and filter either before exporting or after once it is in the spreadsheet.

If you haven't resolved your issue yet and you would like some assistance, just drop me an email: chris@cdssolutions.co.uk

No obligation

Best regards

Christopher Seater

 

Add comment
Log in or register to post comments
Group: Sage 50 Accounts discussion group
An independent forum for Sage users to share ideas, tips and gripes