How can I amend rules for processing a PayPal csv import file that contains currency-conversion records such as to enable clearer balance reports?
24 views
Skip to first unread message
Charles Ambrose
Dec 21, 2024, 11:02:06 AM12/21/24
to hledger
I have a csv import file (x.csv) downloaded from PayPal from which I generate a journal (x.journal) using a rules file (x.csv.rules, a is slightly amended version of the rules file given in the documentation):
❯ hledger -f x.csv print > x.journal
x.journal:
--- snip : start ---
2024-08-07 General card deposit for RefTxnID_001 ; transactionid:Trans_ID_001 fromemail:, type:General card deposit, bankname:ABC BANK AUSTRALIA LIMITED, bankaccount:9406, invoiceid:Inv_ID_001
assets:online:paypal $88.55 = $88.55
assets:current:cja:ABC:123456 $-88.55
2024-08-07 ; transactionid:Trans_ID_002 fromemail:, type:General currency conversion, bankname:, bankaccount:, invoiceid:Inv_ID_001
assets:online:paypal $-88.55 = $0.00
equity:currency conversion $88.55
2024-08-07 DNS Inc. ; transactionid:RefTxnID_001 fromemail:xyz@zxy, type:Pre-approved payment, bankname:, bankaccount:, invoiceid:Inv_ID_001
assets:online:paypal USD-55.00 = USD-55.00
expenses:internet:dns USD55.00
2024-08-07 ; transactionid:Trans_ID_003 fromemail:, type:General currency conversion, bankname:, bankaccount:, invoiceid:Inv_ID_001
assets:online:paypal USD55.00 = USD0.00
equity:currency conversion USD-55.00
--- snip : stop ---
I read this as follows:
1. AUD88.55 is transferred from account ABC:123456 -> online:payal
2. AUD88.55 is converted to USD55.00 (paypal -> currency conversion)
3. USD55.00 is used to pay for the DNS subscription (=transfer from currency conversion -> expenses:internet:dns
If I run a balance report on the journal file I get:
--- snip : start ---
❯ hledger -f x.journal bal
$-88.55 assets:current:cja:ABC:123456
$88.55
USD-55.00 equity:currency conversion
USD55.00 expenses:internet:dns
--------------------
0
--- snip : stop ---
It is not clear from the report that the $88.55 is what got used to pay for the DNS subscription (expenses:internet:dns)
I can manually edit x.journal as follows...
expenses:internet:dns USD55.00 ->
equity:currency:conversion USD55.00
equity:currency conversion $88.55 ->
expenses:internet:dns $88.55
# ... and then re-run balance report to get ...
❯ hledger -f x.journal bal
$-88.55 assets:current:cja:ABC:123456
$88.55 expenses:internet:dns
--------------------
0
... which makes clear that AUD88.55 was used to purchase the DNS subscription.
How can I edit my rules file to achieve a similar end-result?
The most pertinent existing rules are:
--- snip : start ---
if Currency Conversion
account2 equity:currency conversion
...
if
%description_ ^DNS Inc.$
account2 expenses:internet:dns
--- snip : stop ---
Details:
# --- x.csv:
"Date","Time","Time Zone","Description","Currency","Gross","Fee","Net","Balance","Transaction ID","From email","Name","Bank Name","Bank Account","Shipping and handling amount","GST","Invoice ID","Reference Txn ID"
"7/08/2024","14:05:19","Australia/Sydney","General card deposit","AUD","88.55","0.00","88.55","88.55","Trans_ID_001","","","ABC BANK AUSTRALIA LIMITED","9406","0.00","0.00","Inv_ID_001","RefTxnID_001"
"7/08/2024","14:05:19","Australia/Sydney","General currency conversion","AUD","-88.55","0.00","-88.55","0.00","Trans_ID_002","","","","","0.00","0.00","Inv_ID_001","RefTxnID_001"
"7/08/2024","14:05:19","Australia/Sydney","Pre-approved payment","USD","-55.00","0.00","-55.00","-55.00","RefTxnID_001","xyz@zxy","DNS Inc.","","","0.00","0.00","Inv_ID_001","RefTxnID_002"
"7/08/2024","14:05:19","Australia/Sydney","General currency conversion","USD","55.00","0.00","55.00","0.00","Trans_ID_003","","","","","0.00","0.00","Inv_ID_001","RefTxnID_001"
# --- x.csv.rules
# "Date","Time","Time Zone","Description","Currency","Gross","Fee","Net","Balance","Transaction ID","From email","Name","Bank Name","Bank Account","Shipping and handling amount","GST","Invoice ID","Reference Txn ID"
fields date, time, timezone, type, currency, grossamount, feeamount, netamount, balance, transactionid, fromemail, description_, bankname, bankaccount, shipping_and_handling_amount, gst, invoiceid, referencetxnid
skip 1
date-format %-d/%-m/%Y
# ignore some paypal events
if
%type ^In Progress$
%type ^Temporary Hold$
%type ^Update to$
skip
description %description_
# save some other fields as tags
comment transactionid:%transactionid fromemail:%fromemail, time:%time, type:%type, bankname:%bankname, bankaccount:%bankaccount, invoiceid:%invoiceid
# convert to short currency symbols
if %currency AUD
currency $
if %currency EUR
currency E
if %currency GBP
currency P
# generate postings
# the first posting will be the money leaving/entering my paypal account
# (negative means leaving my account, in all amount fields)
account1 assets:online:paypal
amount1 %netamount
# the second posting will be money sent to/received from other party
# (account2 is set below)
amount2 -%grossamount
# if there's a fee, add a third posting for the money taken by paypal.
if %feeamount [1-9]
account3 expenses:banking:paypal
amount3 -%feeamount
comment3 business:
# choose an account for the second posting
# override the default account names:
# if the amount is positive, it's income (a debit)
if %grossamount ^[^-]
account2 income:unknown
# if negative, it's an expense (a credit)
if %grossamount ^-
account2 expenses:unknown
# apply common rules for setting account2 & other tweaks
# --- inlined x-common.rules : start
if
%description_ NETFLIX
account2 expenses:internet:netflix
if
%description_ ^DNS Inc.$
account2 expenses:internet:dns
# --- inlined x-common.rules : stop
# Transfers from/to bank.
if
General card deposit
description %type for %referencetxnid
account2 assets:current:cja:ABC:123456
account1 assets:online:paypal
# Currency conversions
if Currency Conversion
account2 equity:currency conversion
if
%type ^General hold$
%type ^General hold release$
account2 equity:hold-holdrelease
❯ hledger -f x.csv print > x.journal
x.journal:
--- snip : start ---
2024-08-07 General card deposit for RefTxnID_001 ; transactionid:Trans_ID_001 fromemail:, type:General card deposit, bankname:ABC BANK AUSTRALIA LIMITED, bankaccount:9406, invoiceid:Inv_ID_001
assets:online:paypal $88.55 = $88.55
assets:current:cja:ABC:123456 $-88.55
2024-08-07 ; transactionid:Trans_ID_002 fromemail:, type:General currency conversion, bankname:, bankaccount:, invoiceid:Inv_ID_001
assets:online:paypal $-88.55 = $0.00
equity:currency conversion $88.55
2024-08-07 DNS Inc. ; transactionid:RefTxnID_001 fromemail:xyz@zxy, type:Pre-approved payment, bankname:, bankaccount:, invoiceid:Inv_ID_001
assets:online:paypal USD-55.00 = USD-55.00
expenses:internet:dns USD55.00
2024-08-07 ; transactionid:Trans_ID_003 fromemail:, type:General currency conversion, bankname:, bankaccount:, invoiceid:Inv_ID_001
assets:online:paypal USD55.00 = USD0.00
equity:currency conversion USD-55.00
--- snip : stop ---
I read this as follows:
1. AUD88.55 is transferred from account ABC:123456 -> online:payal
2. AUD88.55 is converted to USD55.00 (paypal -> currency conversion)
3. USD55.00 is used to pay for the DNS subscription (=transfer from currency conversion -> expenses:internet:dns
If I run a balance report on the journal file I get:
--- snip : start ---
❯ hledger -f x.journal bal
$-88.55 assets:current:cja:ABC:123456
$88.55
USD-55.00 equity:currency conversion
USD55.00 expenses:internet:dns
--------------------
0
--- snip : stop ---
It is not clear from the report that the $88.55 is what got used to pay for the DNS subscription (expenses:internet:dns)
I can manually edit x.journal as follows...
expenses:internet:dns USD55.00 ->
equity:currency:conversion USD55.00
equity:currency conversion $88.55 ->
expenses:internet:dns $88.55
# ... and then re-run balance report to get ...
❯ hledger -f x.journal bal
$-88.55 assets:current:cja:ABC:123456
$88.55 expenses:internet:dns
--------------------
0
... which makes clear that AUD88.55 was used to purchase the DNS subscription.
How can I edit my rules file to achieve a similar end-result?
The most pertinent existing rules are:
--- snip : start ---
if Currency Conversion
account2 equity:currency conversion
...
if
%description_ ^DNS Inc.$
account2 expenses:internet:dns
--- snip : stop ---
Details:
# --- x.csv:
"Date","Time","Time Zone","Description","Currency","Gross","Fee","Net","Balance","Transaction ID","From email","Name","Bank Name","Bank Account","Shipping and handling amount","GST","Invoice ID","Reference Txn ID"
"7/08/2024","14:05:19","Australia/Sydney","General card deposit","AUD","88.55","0.00","88.55","88.55","Trans_ID_001","","","ABC BANK AUSTRALIA LIMITED","9406","0.00","0.00","Inv_ID_001","RefTxnID_001"
"7/08/2024","14:05:19","Australia/Sydney","General currency conversion","AUD","-88.55","0.00","-88.55","0.00","Trans_ID_002","","","","","0.00","0.00","Inv_ID_001","RefTxnID_001"
"7/08/2024","14:05:19","Australia/Sydney","Pre-approved payment","USD","-55.00","0.00","-55.00","-55.00","RefTxnID_001","xyz@zxy","DNS Inc.","","","0.00","0.00","Inv_ID_001","RefTxnID_002"
"7/08/2024","14:05:19","Australia/Sydney","General currency conversion","USD","55.00","0.00","55.00","0.00","Trans_ID_003","","","","","0.00","0.00","Inv_ID_001","RefTxnID_001"
# --- x.csv.rules
# "Date","Time","Time Zone","Description","Currency","Gross","Fee","Net","Balance","Transaction ID","From email","Name","Bank Name","Bank Account","Shipping and handling amount","GST","Invoice ID","Reference Txn ID"
fields date, time, timezone, type, currency, grossamount, feeamount, netamount, balance, transactionid, fromemail, description_, bankname, bankaccount, shipping_and_handling_amount, gst, invoiceid, referencetxnid
skip 1
date-format %-d/%-m/%Y
# ignore some paypal events
if
%type ^In Progress$
%type ^Temporary Hold$
%type ^Update to$
skip
description %description_
# save some other fields as tags
comment transactionid:%transactionid fromemail:%fromemail, time:%time, type:%type, bankname:%bankname, bankaccount:%bankaccount, invoiceid:%invoiceid
# convert to short currency symbols
if %currency AUD
currency $
if %currency EUR
currency E
if %currency GBP
currency P
# generate postings
# the first posting will be the money leaving/entering my paypal account
# (negative means leaving my account, in all amount fields)
account1 assets:online:paypal
amount1 %netamount
# the second posting will be money sent to/received from other party
# (account2 is set below)
amount2 -%grossamount
# if there's a fee, add a third posting for the money taken by paypal.
if %feeamount [1-9]
account3 expenses:banking:paypal
amount3 -%feeamount
comment3 business:
# choose an account for the second posting
# override the default account names:
# if the amount is positive, it's income (a debit)
if %grossamount ^[^-]
account2 income:unknown
# if negative, it's an expense (a credit)
if %grossamount ^-
account2 expenses:unknown
# apply common rules for setting account2 & other tweaks
# --- inlined x-common.rules : start
if
%description_ NETFLIX
account2 expenses:internet:netflix
if
%description_ ^DNS Inc.$
account2 expenses:internet:dns
# --- inlined x-common.rules : stop
# Transfers from/to bank.
if
General card deposit
description %type for %referencetxnid
account2 assets:current:cja:ABC:123456
account1 assets:online:paypal
# Currency conversions
if Currency Conversion
account2 equity:currency conversion
if
%type ^General hold$
%type ^General hold release$
account2 equity:hold-holdrelease
Charles Ambrose
Dec 25, 2024, 7:27:49 PM12/25/24
to hledger
Having 2 records for a currency conversion -- such as PayPal provides -- makes it difficult to infer costs. Prepending a P directive to the journal enables currency conversion to be done.
In my case, I prepended the following line to x.journal ...
P 2024-08-07 USD $1.61000
Then balance reports can be got (in $ or USD) using value reporting (as specified in the manual) ...
❯ hledger -f x.journal bal -X $
$-88.5500 assets:current:cja:ABC:123456
$88.5500 expenses:internet:dns
--------------------
0
❯ hledger -f x.journal bal -X USD
USD-55.00 assets:current:cja:ABC:123456
USD55.00 expenses:internet:dns
--------------------
0
In my case, I prepended the following line to x.journal ...
P 2024-08-07 USD $1.61000
Then balance reports can be got (in $ or USD) using value reporting (as specified in the manual) ...
❯ hledger -f x.journal bal -X $
$-88.5500 assets:current:cja:ABC:123456
$88.5500 expenses:internet:dns
--------------------
0
❯ hledger -f x.journal bal -X USD
USD-55.00 assets:current:cja:ABC:123456
USD55.00 expenses:internet:dns
--------------------
0
Sorry, my query was a bit premature; I should have read the documentation more thoroughly before posting.
Regards, --Charlie
Regards, --Charlie
Simon Michael
Dec 27, 2024, 10:23:18 PM12/27/24
to Charles Ambrose, hle...@googlegroups.com
Hi.. I didn't follow in full detail, but I think I know roughly what you mean. I am generating similar entries from paypal.
I see you figured out how to get a conversion to base currency by using a market price and -X (--value). A possible downside of this, is that it doesn't affect only that transaction.
To get --infer-costs and -B/--cost working, you have to transform the separate entries like this:
2024-11-27 * T0200 for 7BD00532J84076538
assets:online:paypal € -37.61
equity:conversion € 37.61
2024-11-27 * T0200 for 7BD00532J84076538
assets:online:paypal $ 38.03
equity:conversion $ -38.03
to a single one like this (equity postings together; base currency last, per https://hledger.org/hledger.html#requirements-for-detecting-equity-conversion-postings):
2024-11-27 * T0200 for 7BD00532J84076538
assets:online:paypal € -37.61
equity:conversion € 37.61
equity:conversion $ -38.03
assets:online:paypal $ 38.03
I don't think it can be automated with current CSV rules.
Reply all
Reply to author
Forward
0 new messages