I am preparing an article of tips on exporting data from Sage into Excel. Specifically, I am using File - Send Contents to Excel from the "Financials" screen in Line 50 and have exported about 2,000 records into Excel.
A problem is that Sage exports all amounts as positive numbers.
My solution to this is to make a copy of the "Net" column in the worksheet and call this new column "Net2". Then multiply all Net2 amounts which should be credits by minus 1.
Of the 18 Transaction Types ("Tp") in Sage, 9 should be credits. These are BP, CP, PC, PA, PP, SI, PD, VP, JC (= "bank payments", "cash payments" etc).
I can do this the long way by filtering 9 times on the "Tp" field, then multiplying the "Net2" column by -1. But probably some Excel expert out there can tell me how to do it much faster - ideally the whole lot in one go.
The "Tp" column is column B of the worksheet, "Net" in column J, "Net2" would be column K.
Is there some fast easy way? Macro? IF statement? - but the average accountant has to be able to use it.
Your contribution will be acknowledged in the article.
Thanks in advance
David Carter
Replies (24)
Please login or register to join the discussion.
Blank column has a default text format?
David, if all you get is the formula 'just sitting there', then possibly the column has a default text format, so before you add the formula, format the entire column as general.
Hopefully that will then display the correct answer when you add the tpsign custom formula (nice work Philip!).
Regards
Recognising dates
I note one of the previous comments mentions that on exporting from Sage into Excel, all dates come in as text.
I get round this routinely by highlighting the date column then using find and replace to swop the slashes for more slashes ie (Ctrl-H) find what / replace with /
Dates are then recognised and can be reformatted or sorted as necessary.
Formula Issue
I have been following this one with great interest.
I have gone with Philip Hodgson's formula although I had come up with some of the others myself. Also irritated that I had not thought of Lorna Fellow's solution for dates earlier.
One worry I had: among all the advanced solutions were quite a few formula including lines like:
=A1*(-1)
When
=-A1
would work equally well (and understandable to others).
Just a thought
Simon Maclaren
Philip
I bow to your superior knowledge being in the trade as it were rather than a self taught from a book VBA tinkerer as I am at the end of the day. :-)
VB for Excel - Nice Solutions.....
I always enjoy the VB solutions..
Data Sort By Transaction Type
Highlight Problem Values (presume one column)
Run Macro X
Re-sort if necessary
Job Done !
Macro X is.....
ID start cell (top of highlighted range)
ID finish cell (bottom of highlighted range)
GoTo start Cell
Capture value
Value = [ value *-1 ](ie positive now negative)
Replace New Value
Offsett Down One Cell
Repeat Process
If cell address = finish cell, then stop.
Macro X finished !
Nice and easy.
I use a similiar process to turn "10.00-" into "-10.00" when downloading from my antiquated system.
Not Sure How Many Accounts Know Or Use VB for Excel.
Very Powerful !!
Any Comments ??
Custom function
The line 'Application.Volatile' should NOT be added to the custom function. That is very inefficient as it causes the function to recalculate whenever any data on the worksheet changes.
Without 'Application.Volatile' the function will recalculate only when its input cell changes.
The really cool thing to do is to put the function into a blank workbook and turn it into an add-in. Add-in workbooks are not visible but the functions are still available:
* Click File|Save As and change the 'Save as type' option to 'Microsoft Excel Add-in' (it is at the bottom of the list)
* Close and restart Excel
* Click Tools|Add-Ins and tick your add-in the list (at least a blank workbook must be open or else the command is not available)
The custom function will now be available for all workbooks whenever you start Excel.
I have also made the add-in available for downloading over the Internet at www.vtsoftware.co.uk/tools/vtsage.htm so you do not need to write the function yourself.
Regards
Philip Hodgson
VT Software
[email protected]
Custom worksheet function
A custom function once written gives you the simplest formula on the worksheet and can be used in any workbook. Here's how:
Click Tools|Macro|Visual Basic Editor
In the VB Editor, click Insert|Module
Paste the following code into the module:
Public Function TpSign(ByVal Code As Range) As Long
Select Case Code.Text
Case "BP", "CP", "PC", "PA", "PP", "SI", "PD", "VP", "JC"
TpSign = -1
Case Else
TpSign = 1
End Select
End Function
In the column used to get the sign use the following formula (where the code is in column A):
=TpSign(A1)
Philip Hodgson
VT Software
[email protected]
re Custom Function
you would probably want to insert Application.Volatile at the top to make sure it recalculates automatically.
It would also require someone to go in and edit the code if new codes were added, which a dynamic range named lookup table on a separate sheet would avoid.
You are also potentially going to have to write the code into each new workbook created (unles you store it in PERSONAL.XLS as a general user function. Again it might be easier to have a lookup table pre prepared on a sheet that could be copied into new files.
lookup table
Put all your types in a table with a value of 1 or -1 in the next column.
Then do a vlookup to put the 1 or -1 next to the data.
Multiply the data by 1 or -1 to get the answer you want.
To tidy up you can hide the column with the original data and the +1/-1.
My god its a bind
this is why I moved from sage to QB, i remember when sage was simple and fantastic, has ODBC really been in all versions, one point in sages favour they have improved since the competition upped the ante
Keeping busy!
glad to see you are being even handed, comments are noted re v11 but
there is i believe another problem of a similar nature that you may wish to solve also ( has v11 also corrected this?):
when you have a report on screen and want to email HTML it once again the * - difference is lost, this also seems to happen when you save to a csv file rather than using the file /export method, its very sloppy!
good luck , Nick.
Use Nominal Activity?
Having discovered the send to excel feature following one of your tips, I came across the same problem.
I tend to use nominal activity instead, which gives one debit and one credit column as well as lots of other rubbish...
I create a third column, which is Dr-Cr. Run the formula all the way down, then copy and paste values only. This gives + - numbers. I then delete all the extraneous columns, and proceed to pivot tables.
Send to excel from financials also gives dates as text etc.
Keep up the information flow, very helpful to us users!
Regards
Sage and Excel
Sage L50 version 11 has a module that installs into Excel so that any report in Sage report designer can be run directly into Excel, saving much time and error prone excel coding.
Your tips would still be usefull for people using older versions I guess.
Using Import External Data
We have successfully used the import external data function within excell to import data from Sage Line 50 into Excell which gives you postive figures for debits and minus figures for credits.
You can set up many different queries to import and saves changing anything once it is in excell.
OR and IF
You will need a couple of extras columns or combine the following into a single column.
1) establish if the tp field is a credit..
IF(OR(TpCol="BP",TpCol="CP",..,TpCol="JC"),"Credit","Debit"))
2) Generate Net based on the result....
If(Column from above = "Credit",NetColumn*-1,NetColumn)
The formulae can be reused but there is an not sure if this is the best way to go.
hope this helps Karl
substitution
you need to substitute -1 for the known credit types, and a +1 for the remainder in a simple multiplication formula.
given that you have a column with the tp codes, and you know which are credits, you could create a small table of two columns with all the tp codes, and -1 against the credits and +1 against the debits. you could then use a vlookup against the tp column to get the required multiplier, as part of a multiplication formula.
I you email me a spreadsheet with a sample of data, I will happily email back a solution using this method.
Try a simple array!
How about the following using an array, it is simpler to read than emdedded IFs and easier than a macro.
In effect a more elegant version of what Karl originally suggested.
Assumes the Type is in cell B7 and the value is in K7
=IF(OR(B7={"BP","CP","PC","PA","PP","SI","PD","VP","JC"}),-1,1)*K7
However for large lists the VLOOKUP method is better.
Regards,
Rob Bunce
Or even
On a separate sheet have a lookup table 3 columns wide by n rows and in those list
Column 1 BP, CP, PC, PA, PP, SI, PD, VP, JC
Column 2 narrative that explains the entry "bank payments", "cash payments" etc
Column 3 1 or -1 as necessary
Create a flexible named range by selecting Insert : Name : Define
and setting:
Name as TP_Details
Refers to type the formula
=OFFSET($A$1,0,0,COUNTA($A:$A),3)
and click Add
This assumes that your table above is in columns A:C and will create a variable size range name 3 columns wide and n rows high defined by the COUNTA entry
You can now use the following formula to fix the entry from the net column J
=Jn*VLOOKUP(Bn,TP_Details,3,FALSE)
where n is the relevant row number ie in K2 use the formula
=J2*VLOOKUP(B2,TP_Details,3,FALSE)
I have not included a check for NA. Leaving that out will throw up any new TP details which can be added to the bottom of the TP_Details range on the separate sheet and will automatically fix the NA error as the range will expand to include the new entry as you make it.
OK
this is one of the things i dislike intensely about Sage, they sell you a product that is clearly deficient in a number of ways, export,csv HTML and then they have the gall to charge you for an upgrade which they claim will fix the problems, but often leaves us with a raft of others, hubris.
Custom function
David,
Yes, you are welcome to quote the web address.
I assume you are referring to the add-in you have downloaded, and not one you have made yourself. The instructions on the web page are complete. The Save As stuff is only if you make your own add-in. Yes, you are correct about how to use it.
I know it sounds silly, but have you double checked that the add-in is ticked (not just listed) in the Add-Ins dialog? If it was not ticked when you typed your formula, you will need to retype it.
If it is ticked and you still have a problem, select a blank cell and click Insert|Function and then choose the user-defined category. Does TpSign appear?
If you are still stuck, please email your test workbook to me at [email protected]
I have re-tested downloading the add-in myself and all is OK.
ODBC
Just wanted to say that the ODBC feature that has been available in ALL versions of Sage allows you to produce spreadsheets in a much simpler way than exporting it and would have not caused the problem you faced with having to amend the data in any way. It can automatically be refreshed to get the up to date figures at the press of a button.
It is a totally unused facility but brilliant for extracting information from Sage which you cant get easily any other way!!
In Excel, run Data, get external data,new database query and take it from there..