Solved Income or Expense

[Gasman]

Hi everyone,

I still do 4 hours admin work a week for my old bosses.
I am doing it more as a favour to them, as they treated me well when I worked for them, and when they had to let me go, due to lack of work.

They are giving up a system, that they do not use to any great extent, except for the fact it gave them a Profit and Loss report for the properties they own.

They use the Xero accounting system, or at least myself and the accountants do.

So I was thinking of exporting the data from Xero, initially to Excel for a pivot table for P&L.
However I discovered that Xero does not surround text fields with ", and the item codes being 0001 or 001 all end up in Excel as 1.

So I imported into Access with a spec, and have the item code as text and can keep the actual data intact.

Xero exports the data as
Bill postive
Bill Credit Note negative
Sales Invoice positive
Sales Invoice Credit Note negative

Also when entering the data, while rent for a property is Sales invoice and positive, the agent commission fee is also classed as Sales Invoice (as it is in the same transaction), but is negative, so I would class that as an expense.? The opposite applies for any Bill Credit Note?

Now I know I can create a function to determine what category a transaction should be in Income or Expense, but was wondering whether a table could do the job.?

I was thinking of
TypeID
XeroType
AmountMin
AmountMax
ProfitType

and use a DLookUp(), but I know they really should not be used in queries. My plan is purely to use Access DB as a datasource and Excel for the P&L, mainly for time constraints. For that I would create a query with just the relevant fields for the Excel pivot table.

Is there any other way I can determine what a transaction should be classed as besides the two methods I have just mentioned.
Whilst I can join the export table with the Type table via XeroType, I do not believe I can deduce the category with amounts between A and B unless using a DLookUp().

So pretty much asking, 'how you would approach this problem?'

TIA

 

[Pat Hartman]

However I discovered that Xero does not surround text fields with ", and the item codes being 0001 or 001 all end up in Excel as 1.

It wouldn't matter. Excel does not respect the quotes. It is too smart for us and insists on treating things that look like numbers as numbers. Even when you use the correct import method for .csv files. Not sure if that has always been the case but it is now. Try to import zip codes. Too bad for you if you live in New England.

 

[Gasman]

Ah well.
I've created a function for now, as I realised the amount is not a factor, just the description. So that means I can create a tblPLType and link to that, or that is the plan at the moment.
Just did that and works as hoped.

Seems a faff, but linking in Access saves all the VLookUps in Excel.
I am not going to normalize the data, it is in Access purely for me to manipulate as I need for Excel. Might run a few P&L reports for the directors. Not doubt they will ask some time. I am just trying to preempt their requests.

 

[Gasman]

Just reread my post and realised amount is a factor.

 

[Pat Hartman]

The only way I've been able to preserve leading zeros is to create a template with the columns formatted to text rather than general when I want numbers to have leading zeros. Then instead of using TransferText, I have to append the data using OLE.

 

[Gasman]

Well I expect I will end up creating several reports for them in Access, so the data needs to be in Access anyway.