We have a financial csv file containing financial details that will be extracted from paypal each month.
Your job would be to process this file and categorize and summarize the contents.
You would maintain in a simple database (could just be a text file) a list of rules concerning categorization and report accordingly.
Essentially this is just a data extraction project with the ability of the user to add/modify/delete their own categories and choose which field(s) and part of/all contents of these fields are “defined” as a category.
This gives the ability to assign their own categories to any transaction in the csv file.
The CSV file contains both debits and credits (sales, money transfers, refunds, purchases, etc).
A debit transaction is a minus figure, a credit transaction is a positive figure.
Fee’s are paypal charges in a separate field and need to be tracked as well.
To categorize a specific transaction the user should select
a) debit or credit
b) The Type e.g. “Refund” or “Payment Sent” or “Payment Received” or “Subscription Payment Received” (there are many others) – This maps to a field in the CSV file.
In the case of debits
c) The name (another csv field) would “map” to a specific user specified category
d) the currency (USD, AUD, etc), gross amount, fees, and nett figures (all csv fields) would be retrieved
In the case of credits
e) The item title (if NOT empty) is the major classification for category
otherwise if title is empty then the name and from email address is used as the category
f) the currency (USD, AUD, etc), gross amount, fees, and nett figures
In the case of e) and f) all are csv fields.
Some types see b) above are not to be included in the processing (you would ignore this record and not include it in figures).
Unless a record is “excluded” as per the rule about it should always be included.
For each “category” of records about you maintain a separate running total.
Note the “Category” shown here is the user entered category (see below).
At the end of the file, you would create a summary file showing all categories, number of entries per category, and amounts (totals, and fees).
You would also create a new csv file containing a category code, date, and transaction reference (unique field in original CSV file).
Effectively this is all the “records” which have been processed, but assigned to their categories.
Another file would also be created containing all records that a category could not be found for (no rules were entered by the user). This is so the user could see the records, and then enter rules for these records, and re-process the csv file. Eventually this file would be empty as all records got categories.
All user specified categories would need to be permanently recorded in a database (might just be a text file) so that future processing uses these settings.
The user needs to be able to add/edit/delete these categories.
Finally, there needs to be a file that is created to record any tax payable or deductable for australian orders.
If an order is placed for a business in australia, 10% GSt is applicable, and if a customer makes a purchase and the live in australia, 10% GST is applicable.
This file would contain any records that meet this criteria so the relevant amount can be submitted to the tax department.
Both the details of each transaction and the overall total of GST for debits and total of GST for credits needs to be recorded, together with all transactions.
Determining whether a record is needing to be included as a “GST” transaction involves looking for several possibilities searches (all in the original csv file).
1) If the Country field has data in it, include if the value is “Australia”
2) If the email address for the record has “.com.au” as part of it.
3) If another field says “Non-US”.
It will be a combination of 1 or more of these parameters.
There may be more minor functionality than listed here (to do with categories) but this is effectively what we are looking for.
The program can have a GUI Interface or just be command driven.
Any language is fine, but please indicate which language you will be using.
Thanks
