Files
@ 01a86107d7e4
Branch filter:
Location: NPO-Accounting/import2ledger/README.rst
01a86107d7e4
27.8 KiB
text/x-rst
benevity: Add support for latest Benevity format.
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 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 | import2ledger
=============
Introduction
------------
import2ledger provides a Python library and CLI tool to read financial data from various popular sources and generate entries for text-based accounting books from them.
Installation
------------
This is a pretty normal Python module, so if you have a favorite way to install those, it will probably work. If you just want to install it to run the script locally, try running from the source directory::
$ python3 -m pip install --user -U --upgrade-strategy only-if-needed .
Configuring import2ledger
-------------------------
Since different organizations follow different accounting rules, you need to define an entry template for each kind of data that you want to be able to import. You do that in a configuration file. By default, import2ledger reads a configuration file at ``~/.config/import2ledger.ini``. You can specify a different path with the ``-C`` option, and you can specify that multiple times to read multiple configuration files in order.
Writing templates
~~~~~~~~~~~~~~~~~
A template looks like a Ledger entry with a couple of differences:
* You don't write the first payee line.
* Instead of writing specific currency amounts, you write simple expressions to calculate amounts.
Here's a simple template for Patreon patron payments::
[DEFAULT]
patreon income ledger entry =
;Tag: Value
Income:Patreon -{amount}
Accrued:Accounts Receivable:Patreon {amount}
Let's walk through this line by line.
Every setting in your configuration file has to be in a section. ``[DEFAULT]`` is the default section, and import2ledger reads configuration settings from here if you don't specify another one. This documentation explains how to use sections later.
``patreon income ledger entry =`` specifies which entry template this is. Every template is found from a setting with a name in the pattern ``<SOURCE> <TYPE> ledger entry``. The remaining lines are indented further than this name; this defines a multiline value. Don't worry about the exact indentation of your template; import2ledger will indent its output nicely.
The first line of the template is a Ledger tag. The program will leave all kinds of tags and Ledger comments alone, except to indent them nicely.
The next two lines split the money across accounts. They follow almost the same format as they do in Ledger: there's an account named, followed by a tab or two or more spaces, and then an expression. Each time import2ledger generates an entry, it will evaluate this expression using the data it imported to calculate the actual currency amount to write. Your expression can use:
* numbers and quoted strings
* imported data referred to as ``{variable_name}``
* basic arithmetic operators (including parentheses for grouping)
* the operators ``==``, ``!=``, ``<``, ``<=``, ``>``, ``>=``, ``and``, ``or``, ``not``, and ``in``
* conditional expressions in the format ``TRUE_EXPR if CONDITION else FALSE_EXPR``
import2ledger uses decimal math to calculate each amount, and rounds to the number of digits appropriate for that currency. If the amount of currency being imported doesn't split evenly, spare change will be allocated to the last split to keep the entry balanced on both sides.
Refer to the `Python documentation for INI file structure <https://docs.python.org/3/library/configparser.html#supported-ini-file-structure>`_ for full details of the syntax. Note that import2ledger doesn't use ``;`` as a comment prefix, since that's the primary comment prefix in Ledger.
Template variables
~~~~~~~~~~~~~~~~~~
import2ledger templates have access to a few variables for each transaction that can be included anywhere in the entry, not just amount expressions. In other parts of the entry, they're treated as strings, and you can control their formatting using `Python's format string syntax <https://docs.python.org/3/library/string.html#formatstrings>`_. For example, this template customizes the payee line and sets different payees for each side of the transaction::
patreon income ledger entry =
{date} Patreon payment from {payee}
Income:Patreon -{amount}
;Payee: {payee}
Accrued:Accounts Receivable:Patreon {amount}
;Payee: Patreon
Templates automatically detect whether or not you have a custom payee line by checking if the first line begins with a date variable. If it does, it's assumed to be your payee line. Otherwise, the template uses a default payee line of ``{date} {payee}``.
Every template can use the following variables:
================== ==========================================================
Name Contents
================== ==========================================================
amount The total amount of the transaction, as a simple decimal
number (not currency-formatted)
------------------ ----------------------------------------------------------
currency The three-letter code for the transaction currency
------------------ ----------------------------------------------------------
date The date of the transaction, in your configured output
format
------------------ ----------------------------------------------------------
payee The name of the transaction payee
------------------ ----------------------------------------------------------
source_abspath The absolute path of the file being imported
------------------ ----------------------------------------------------------
source_absdir The absolute path of the directory that contains the file
being imported
------------------ ----------------------------------------------------------
source_dir The path of the directory that contains the file being
imported, as specified on the command line
------------------ ----------------------------------------------------------
source_name The filename of the file being imported
------------------ ----------------------------------------------------------
source_path The path of the file being imported, as specified on the
command line
------------------ ----------------------------------------------------------
source_stem The filename of the file being imported, with its
extension removed
================== ==========================================================
Specific importers and hooks may provide additional variables.
Supported templates
~~~~~~~~~~~~~~~~~~~
You can define the following templates.
Amazon Affiliate Program
^^^^^^^^^^^^^^^^^^^^^^^^
``amazon earnings ledger entry``
Imports one transaction per month, summarizing all earnings over the month. Generated from Amazon's "Fee Earnings" report CSV.
Benevity
^^^^^^^^
``benevity donations ledger entry``
Imports one transaction per row in Benevity's donations report CSV.
This template can use these variables:
================ ===========================================================
Name Contents
================ ===========================================================
comment The comment from the donor
---------------- -----------------------------------------------------------
corporation The name of the participating corporation
---------------- -----------------------------------------------------------
disbursement_id The ID of the disbursement from Benevity that includes this
donation
---------------- -----------------------------------------------------------
donation_amount The amount donated by the individual donor named
---------------- -----------------------------------------------------------
donation_fee The fee taken from the donation.
This information was not available in reports before 2019,
so this amount will always be 0.00 in those reports.
---------------- -----------------------------------------------------------
fee_comment The note from the Fee Comment column in the report.
This information was not available in reports before 2020,
so this field will always be empty in those reports.
---------------- -----------------------------------------------------------
frequency The frequency of this donation as indicated in the report
---------------- -----------------------------------------------------------
match_amount The amount of the donation match by the participating
corporation
---------------- -----------------------------------------------------------
match_fee The fee taken from the donation match.
This information is not available in reports from
before 2019 or since 2020, so this amount will always be
0.00 in those reports.
---------------- -----------------------------------------------------------
merchant_fee The merchant fee taken from the donation total.
This information was not available in reports before 2019,
so this amount will always be 0.00 in those reports.
---------------- -----------------------------------------------------------
net_amount The net amount Benevity owes the charity for this
transaction: the donation plus the match minus the fees
---------------- -----------------------------------------------------------
project The Project named in this row
---------------- -----------------------------------------------------------
transaction_id The ID of this specific donation
================ ===========================================================
BrightFunds
^^^^^^^^^^^
``brightfunds donorreport ledger entry``
Imports one transaction per row in donor report XLS files that BrightFunds mails each month to recipients.
This template can use these variables:
================ ===========================================================
Name Contents
================ ===========================================================
company_name The company name as reported in the spreadsheet
---------------- -----------------------------------------------------------
corporation The company name as detected by the importer (this is
usually what you want)
---------------- -----------------------------------------------------------
donor_name The donor name as reported in the spreadsheet (usually you
want to use ``payee`` instead)
---------------- -----------------------------------------------------------
donor_email The donor's e-mail address as reported in the spreadsheet
---------------- -----------------------------------------------------------
on_behalf_of From the corresponding spreadsheet column
---------------- -----------------------------------------------------------
fund From the corresponding spreadsheet column
---------------- -----------------------------------------------------------
type From the corresponding spreadsheet column
================ ===========================================================
Eventbrite
^^^^^^^^^^
``eventbrite sales ledger entry``
Imports one transaction per ticket sale transaction. Generated from CSV export of Eventbrite sales report.
NOTE: This importer currently only imports sales in the Completed status. In particular, it does not import refunds. It may be extended in the future.
This template can use these variables. Note that many of the informational fields may be empty if their corresponding columns do not appear in the export.
================ ===========================================================
Name Contents
================ ===========================================================
attendee_id From the ``Attendee #`` spreadsheet column
---------------- -----------------------------------------------------------
corporation From the ``Company`` spreadsheet column
---------------- -----------------------------------------------------------
event_id From the corresponding spreadsheet column
---------------- -----------------------------------------------------------
event_name From the corresponding spreadsheet column
---------------- -----------------------------------------------------------
eventbrite_fees From the corresponding spreadsheet column
---------------- -----------------------------------------------------------
order_id From the ``Order #`` spreadsheet column
---------------- -----------------------------------------------------------
payment_fees From the ``Eventbrite Payment Processing`` spreadsheet
column
---------------- -----------------------------------------------------------
quantity From the corresponding spreadsheet column
---------------- -----------------------------------------------------------
tax From the ``Tax Paid`` spreadsheet column
---------------- -----------------------------------------------------------
ticket_type From the corresponding spreadsheet column
---------------- -----------------------------------------------------------
total_fees From the ``Fees Paid`` spreadsheet column
================ ===========================================================
O'Reilly Media
^^^^^^^^^^^^^^
``oreilly payments ledger entry``
Imports one transaction per payment. Generated from CSV export of O'Reilly's payment history.
This template can use these variables:
================ ===========================================================
Name Contents
================ ===========================================================
paid_date From the corresponding spreadsheet column. This is usually
the end of the month, while the actual transaction date
might be slightly off.
================ ===========================================================
``oreilly royalties ledger entry``
Imports one transaction per royalty period (usually a month, historically a quarter). Generated from CSV export of O'Reilly's royalties statement.
This template can use these variables:
================ ===========================================================
Name Contents
================ ===========================================================
start_date From the corresponding spreadsheet column
---------------- -----------------------------------------------------------
paid_date From the corresponding spreadsheet column. This
corresponds to a row in the payment history as well.
================ ===========================================================
Patreon
^^^^^^^
``patreon income ledger entry``
Imports one transaction per patron per month. Generated from Patreon's monthly patron report CSVs.
``patreon payout ledger entry``
Imports one transaction per month for that month's payout. Generated from Patreon's payout report CSV.
This template can use these variables:
=============== ===========================================================
Name Contents
=============== ===========================================================
pledges_amount A decimal with the amount paid out by Patreon to pledges
you've made
--------------- -----------------------------------------------------------
transfer_amount A decimal with the amount paid out by Patreon to your bank
account
=============== ===========================================================
``patreon cardfees ledger entry``
Imports one expense transaction per month for that month's credit card fees. Generated from Patreon's earnings report CSV.
``patreon servicefees ledger entry``
Imports one expense transaction per month for that month's Patreon service fees. Generated from Patreon's earnings report CSV.
``patreon vat ledger entry``
Imports one transaction per country per month each time Patreon withheld VAT. Generated from Patreon's VAT report CSV.
This template can use these variables:
============== ============================================================
Name Contents
============== ============================================================
country_name The full name of the country VAT was withheld for
-------------- ------------------------------------------------------------
country_code The two-letter ISO country code of the country VAT was
withheld for
============== ============================================================
Stripe
^^^^^^
``stripe payment ledger entry``
Imports one transaction per payment. Generated from Stripe's payments CSV export.
This template can use these variables:
============== ==========================================================
Name Contents
============== ==========================================================
customer_id The id assigned to this customer by Stripe
-------------- ----------------------------------------------------------
customer_email The customer's e-mail address
-------------- ----------------------------------------------------------
description The description given to the payment when it was created
-------------- ----------------------------------------------------------
fee The amount of fees charged by Stripe for this payment, as
a plain decimal number
-------------- ----------------------------------------------------------
payment_id The id assigned to this payment by Stripe
-------------- ----------------------------------------------------------
payout_id The id of the associated Stripe payout
-------------- ----------------------------------------------------------
tax The amount of tax withheld by Stripe for this payment, as
a plain decimal number
============== ==========================================================
``stripe payout ledger entry``
Imports one transaction per payment. Generated from Stripe's payouts CSV export.
This template can use these variables:
========================== ==============================================
Name Contents
========================== ==============================================
adjustment_count Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
adjustment_gross Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
adjustment_fees Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
adjustment_net Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
balance_txid Stripe ID of the balance transaction
-------------------------- ----------------------------------------------
collected_fee_count Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
collected_fee_gross Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
collected_fee_refund_count Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
collected_fee_refund_gross Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
destination_id Stripe ID of the payout destination account
-------------------------- ----------------------------------------------
failure_txid Stripe ID of the failure balance transaction
-------------------------- ----------------------------------------------
payment_count Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
payment_gross Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
payment_fees Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
payment_net Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
payout_id Stripe ID of this payout
-------------------------- ----------------------------------------------
refund_count Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
refund_gross Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
refund_fees Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
refund_net Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
total_count Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
total_gross Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
total_fees Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
total_net Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
validation_count Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
validation_fees Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
retried_payout_count Decimal from the corresponding CSV column
-------------------------- ----------------------------------------------
retried_payout_net Decimal from the corresponding CSV column
========================== ==============================================
YourCause
^^^^^^^^^
``yourcause donations ledger entry``
Imports one transaction per row in YourCause's donations report CSV.
This template can use these variables:
================= ==========================================================
Name Contents
================= ==========================================================
comment The comment from the donor
----------------- ----------------------------------------------------------
corporation The name of the participating corporation
----------------- ----------------------------------------------------------
dedication Text from the corresponding CSV column
----------------- ----------------------------------------------------------
dedication_type Text from the corresponding CSV column
----------------- ----------------------------------------------------------
designation Text from the corresponding CSV column
----------------- ----------------------------------------------------------
donor Name of the individual who donated
----------------- ----------------------------------------------------------
donor_amount The amount donated by the individual donor named
----------------- ----------------------------------------------------------
match_amount The amount of the donation match by the participating
corporation
----------------- ----------------------------------------------------------
original_amount The amount of the original donation in the original
currency
----------------- ----------------------------------------------------------
original_currency The local currency the original donation was made in
----------------- ----------------------------------------------------------
payment_id The ID of the payment from YourCause that includes this
donation
----------------- ----------------------------------------------------------
received_amount Decimal from the corresponding CSV column
----------------- ----------------------------------------------------------
transaction_id The ID of this donation from YourCause
================= ==========================================================
Other output options
~~~~~~~~~~~~~~~~~~~~
Various options control how import2ledger formats dates and currency amounts. Run ``import2ledger --help`` for a full list; they're listed under the "default overrides" section. You can specify these in your configuration file, too. Just change any dashes (``-``) in the option name to underscores (``_``), and separate the switch name from your value with ``=``. For example, add this to your configuration file to use ISO 8601-formatted dates in your Ledger entries::
date_format = %%Y-%%m-%%d
(``%`` is a special character in the configuration file syntax. ``%%`` represents a literal percent sign.)
Configuration sections
~~~~~~~~~~~~~~~~~~~~~~
If you keep different sets of books, it might be helpful to have different configuration settings for each. For example, you might adjust the templates for each set of books to use different accounts or tags. Or you might just need to change the formatting of dates or currency amounts.
import2ledger makes this easy by letting you define a new section of options, and then using them all when you import transactions. For example, say you keep two sets of books, one for US accounts and another for European accounts. You could write a configuration file like::
[us]
date_format = %%m/%%d/%%Y
signed_currencies = USD
[eu]
date_format = %%d.%%m.%%Y
signed_currencies = EUR
When you run import2ledger, you can tell it to load options from one of these sections with the option ``--use-config NAME``, or ``-c NAME`` for short. In this example, ``import2ledger -c eu`` would load the settings for your European books. import2ledger looks for configuration settings in the following places, and uses the first setting it finds:
1. The configuration section you specify with ``-c``
2. Command-line options
3. The ``[DEFAULT]`` configuration section
4. import2ledger defaults
Running import2ledger
---------------------
Once you've written a configuration file with your templates and any other configuration you need, you can run it with data to import::
$ import2ledger [options …] file1.csv [file2.csv …]
import2ledger will read all the data sources and generate bookkeeping entries from them.
import2ledger needs to be able to seek within the source files. Because of that, your input files need to be normal files, or symlinks to them. Special files like devices and FIFOs aren't supported by import2ledger.
Exit status
~~~~~~~~~~~
import2ledger reports the following exit codes:
========= =================================================================
Exit code Meaning
========= =================================================================
0 Imported data from all source files
--------- -----------------------------------------------------------------
1 Internal error (probably a bug)
--------- -----------------------------------------------------------------
2 Error in the user's settings, such as a formatting error in a
template or date, or a reference to a nonexistent file or
configuration file section
--------- -----------------------------------------------------------------
11-99 Failed to import data from at least one source file. The exit
code is 10 + the number of files that couldn't be imported, up
to the maximum exit code of 99.
========= =================================================================
|