Hello everyone!
I need to write importer that will
1 categorize sale and refund transactions dividing them per division
2 aggregate them
3 other transactions (non sale or refund) provide in regular way (1 leg) so
later smart importer will be able to categorize them.
Approximate CSV file format (simplified, in real file transactions go in
random order and are more numerous.)
Date Type Narration Gross Fee Net
4/1/2019 Sale Product by DIVB 100 -5 95
4/2/2019 Sale Product by DIVB 50 -2.50 47.50
4/3/2019 Refund Product by DIVB -50 2 -48
4/11/2019 Sale Product by DIVC 40 -2 38
4/12/2019 Sale Product by DIVC 20 -1 19
4/13/2019 Refund Product by DIVC -20 0 -20
4/21/2019 Payment Some Expense -5 0 -5
4/22/2019 Payment Some Expense -10 0 -10
4/23/2019 Payment Some Expense -15 0 -15
Needed Output:
2019-04-21 * "Some Expense"
Assets:Paypal -5.00 USD
2019-04-22 * "Some Expense"
Assets:Paypal -10.00 USD
2019-04-23 * "Some Expense"
Assets:Paypal -15.00 USD
2019-04-30 * "Paypal Sales Apr 2019 DIVB"
Income:DIVB:Paypal:Revenue:Sales -150 USD
Expenses:DIVB:Paypal:Fees:TransactionFees 7.50 USD
Income:DIVB:Paypal:Revenue:Refunds 50 USD
Expenses:DIVB:Paypal:Fees:RefundedFees -2 USD
Assets:Paypal 94.50 USD
2019-04-30 * "Paypal Sales Apr 2019 DIVC"
Income:DIVC:Paypal:Revenue:Sales -60 USD
Expenses:DIVC:Paypal:Fees:TransactionFees 3 USD
Income:DIVC:Paypal:Revenue:Refunds 20 USD
Assets:Paypal 37 USD
I am trying to understand what's the best way to approach it. Can i build
on functionality of default CVS importer ?
So far i have the following plan:
1 Since i will have several files with identical format whose contents
won't allow to match importer to file,
i will use file name regex to do that. "fnregex".I wil need to customize
the class to make identify() match on fnregex
2 I will write/use "txnregex" regex to get sale/refund transactions and
categorize them. (regex will work on NARRATION and SUPPL, suppl is
"supplementory" - used only for regex purporse)
I will need to add additional functionality to default CVS importer
(amount1, amount2, amount3 / account1, account 2, account 3)
3 I will need to write "Aggregate" to aggregate transactions.
4 To get remaining transactions i can either try to use NOT regex negative
for the ones used for DIVB and DIVC, or possibly create temporary file
deliting transactions from step 2.
Resulting config will look something like this:
CONFIG = [
# Paypal_CO
csv.Importer (
# Division B Aggregated Sales and Refunds
Aggregate(
# Division B Sales
({
csv.Col.DATE: 'Date',
csv.Col.NARRATION: 'Narration',
csv.Col.SUPPL: 'Type',
csv.Col.AMOUNT1: 'Gross',
csv.Col.AMOUNT2: 'Fee',
csv.Col.AMOUNT3: 'Net',
},
account1 = 'Income:DIVB:Paypal:Revenue:Sales',
account2 = 'Expenses:DIVB:Paypal:Fees:TransactionFees',
account3 = 'Assets:Paypal',
txnregex = '^(?=.*\DIVB\b)(?=.*\Sale\b).*$'),
# Division B Refunds
({
csv.Col.DATE: 'Date',
csv.Col.NARRATION: 'Narration',
csv.Col.SUPPL: 'Type',
csv.Col.AMOUNT1: 'Gross',
csv.Col.AMOUNT2: 'Fee',
csv.Col.AMOUNT3: 'Net',
},
account1 = 'Income:DIVB:Paypal:Revenue:Refunds',
account2 = 'Expenses:DIVB:Paypal:Fees:RefundedFees',
account3 = 'Assets:Paypal',
txnregex = '^(?=.*\DIVB\b)(?=.*\Refund\b).*$'),
)
# Division C Aggregated Sales and Refunds
Aggregate(
# Division C Sales
({
csv.Col.DATE: 'Date',
csv.Col.NARRATION: 'Narration',
csv.Col.SUPPL: 'Type',
csv.Col.AMOUNT1: 'Gross',
csv.Col.AMOUNT2: 'Fee',
csv.Col.AMOUNT3: 'Net',
},
account1 = 'Income:DIVC:Paypal:Revenue:Sales',
account2 = 'Expenses:DIVC:Paypal:Fees:TransactionFees',
account3 = 'Assets:Paypal',
txnregex = '^(?=.*\DIVC\b)(?=.*\Sale\b).*$'),
# Division C Refunds
({
csv.Col.DATE: 'Date',
csv.Col.NARRATION: 'Narration',
csv.Col.SUPPL: 'Type',
csv.Col.AMOUNT1: 'Gross',
csv.Col.AMOUNT2: 'Fee',
csv.Col.AMOUNT3: 'Net',
},
account1 = 'Income:DIVC:Paypal:Revenue:Refunds',
account2 = 'Expenses:DIVC:Paypal:Fees:RefundedFees',
account3 = 'Assets:Paypal',
txnregex = '^(?=.*\DIVC\b)(?=.*\Refund\b).*$'),
)
# Remaining Transactions
({
csv.Col.DATE: 'Date',
csv.Col.NARRATION: 'Narration',
csv.Col.SUPPL: 'Type',
csv.Col.AMOUNT1: 'Gross',
},
account1 = 'Assets:Paypal'),
currency = 'USD',
fnregex = 'Paypal_CO.*\.CSV'
),
]
Would like to hear your suggestions and critique.
--
You received this message because you are subscribed to the Google Groups
"Beancount" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/beancount/1afa2c6f-9b3b-4c3b-a8fd-354b229df80f%40googlegroups.com.
