-
Notifications
You must be signed in to change notification settings - Fork 3
/
oc.csv.rules
84 lines (73 loc) · 4.68 KB
/
oc.csv.rules
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
# * hledger (1.27) conversion rules for OpenCollective CSV v2
# https://hledger.org/hledger.html#csv-format
#
# These are the rules used to convert data from the hledger collective.
# They should work pretty well for any collective, except you might
# need to change the fiscal host name "open-source-collective" where
# it's hard-coded below.
#
# OC's CSV data format changed in 2021. These rules cover both old and
# new formats, which complicates them but simplifies conversion. For
# data that's only on one side of 2021-06-01, you could simplify these rules.
skip 1
newest-first
fields date, shortId, shortGroup, description, type, kind, isRefund, isRefunded, shortRefundId, displayAmount, amount, paymentProcessorFee, netAmount, balance_, currency_, accountSlug, accountName, oppositeAccountSlug, oppositeAccountName, paymentMethodService, paymentMethodType, expenseType, expenseTags, payoutMethodType, merchantId
date-format %Y-%m-%dT%H:%M:%S
# ** Before 2021-06-01, host fee is not shown explicitly in the CSV.
# "datetime","shortId","shortGroup","description","type","kind","isRefund","isRefunded","shortRefundId","displayAmount","amount","paymentProcessorFee","netAmount","currency","accountSlug","accountName","oppositeAccountSlug","oppositeAccountName","paymentMethodService","paymentMethodType","expenseType","expenseTags","payoutMethodType","merchantId"
# "2021-05-01T01:13:51","46974380","8aa606bd","Monthly contribution from October Swimmer (Sponsors)","CREDIT","CONTRIBUTION","","","","$100.00 USD",100,-3.2,86.8,"USD","hledger","hledger","october-swimmer","October Swimmer","STRIPE","CREDITCARD",,"",,
# < 2021-06-01
if %date ^(20[01]|2020|2021-0[1-5])
comment id:%shortId, group:%shortGroup, payment-method:%paymentMethodService, payment-type:%paymentMethodType
amount1 -%amount %currency_
account1 revenues:donations:%oppositeAccountSlug
amount2 %netAmount %currency_
account2 assets:opencollective:%accountslug
balance2 %balance_ %currency_
amount3 -%paymentProcessorFee %currency_
account3 expenses:fees:%paymentMethodService%payoutMethodType
account4 expenses:fees:open-source-collective
# ^ our fiscal host name, hard-coded
# ** From 2021-06-01, host fee appears as a separate transaction.
# "datetime","shortId","shortGroup","description","type","kind","isRefund","isRefunded","shortRefundId","displayAmount","amount","paymentProcessorFee","netAmount","currency","accountSlug","accountName","oppositeAccountSlug","oppositeAccountName","paymentMethodService","paymentMethodType","expenseType","expenseTags","payoutMethodType","merchantId"
# "2021-06-01T02:08:37","65365e36","6b474b38","Monthly contribution from October Swimmer (Sponsors)","CREDIT","CONTRIBUTION","","","","$100.00 USD",100,-3.2,96.8,"USD","hledger","hledger","october-swimmer","October Swimmer","STRIPE","CREDITCARD",,"",,
# "2021-06-01T02:08:37","95a61220","6b474b38","Host Fee to Open Source Collective","DEBIT","HOST_FEE","","","","-$10.00 USD",-10,0,-10,"USD","hledger","hledger","opensource","Open Source Collective",,,,"",,
# >= 2021-06-01
# In 2022 downloads, the balance field's values didn't work, but in 2023 it seems fixed.
if %date ^(2021-(0[6-9]|1)|202[2-9]|20[3-9]|2[1-9]|[3-9])
& %kind CONTRIBUTION
comment id:%shortId, group:%shortGroup, payment-method:%paymentMethodService, payment-type:%paymentMethodType
amount1 -%amount %currency_
account1 revenues:donations:%oppositeAccountSlug
amount2 %netAmount %currency_
account2 assets:opencollective:%accountslug
balance2 %balance_ %currency_
amount3 -%paymentProcessorFee %currency_
account3 expenses:fees:%paymentMethodService%payoutMethodType
if %type DEBIT
account1 expenses:misc:%oppositeAccountSlug
if %description bounty
account1 expenses:bounties:%oppositeAccountSlug
if %date ^(2021-(0[6-9]|1)|202[2-9]|20[3-9]|2[1-9]|[3-9])
& %kind EXPENSE
comment id:%shortId, group:%shortGroup, payment-method:%paymentMethodService, payment-type:%paymentMethodType
amount1 -%amount %currency_
amount2 %netAmount %currency_
account2 assets:opencollective:%accountslug
amount3 -%paymentProcessorFee %currency_
account3 expenses:fees:%paymentMethodService%payoutMethodType
if %date ^(2021-(0[6-9]|1)|202[2-9]|20[3-9]|2[1-9]|[3-9])
& %kind HOST_FEE
comment id:%shortId, group:%shortGroup
amount2 %netAmount %currency_
account2 assets:opencollective:%accountslug
amount1 -%amount %currency_
account1 expenses:fees:open-source-collective
# ^ fiscal host name, could use %oppositeAccountSlug
# ** Misc (all dates)
# When neither paymentMethodService or paymentMethodType is known, use a generic value
if %kind (CONTRIBUTION|EXPENSE)
& %paymentProcessorFee ^[^0]
& %paymentMethodService ^$
^ %payoutMethodType ^$
account3 expenses:fees:unknown-processor