search

ursjoss / lola-sumup

CLI to evaluate the monthly sumup CSV file and export useful LoLa reports
MIT License
1 stars 0 forks source link

readme

lola-sumup

CLI to evaluate the monthly SumUp CSV extracts and extract LoLa specific reports.

Summary

The cli application lola-sumup has two subcommands: prepare and export.

The prepare subcommand parses two SumUp extracts with monthly data and creates an intermediate file, combining data from both reports, enriched with three columns Topic, Owner, and Purpose. The user may redact the content of those three columns, as the simple heuristics may not get it right out of the box.

The (potentially redacted) intermediate file is consumed by the second export step. It generates three different exports from it, dedicated to different purposes in the context of LoLa's monthly closing process.

Versions

The format of the sumup sales reports has changed. lola-sumup has been adapted in a non-backwards-compatible way.

Version Sumup-exports
0.1.0 Up to April 2024
0.2.0 As of May 2024

CLI

The lola-sumup command has two subcommands:

A cli program to create exports from sumup transactions exported in CSV format

Usage: lola-sumup <COMMAND>

Commands:
  prepare  Prepares an enriched intermediate file from the original sumup sales report CSV and transaction report CSV
  export   Consumes the (potentially redacted) intermediate file and exports to different special purpose CSV files
  help     Print this message or the help of the given subcommand(s)

Options:
  -h, --help     Print help
  -V, --version  Print version

The prepare step

The lola-sumup prepare command works as follows:

Prepares an enriched intermediate file from the original sumup sales report CSV and transaction report CSV

Usage: lola-sumup prepare --sales-report <SALES_REPORT> --transaction-report <TRANSACTION_REPORT> <MONTH>

Arguments:
  <MONTH>  the month for which transactions are to be processed (<yyyymm>, e.g. 202305)

Options:
  -s, --sales-report <SALES_REPORT>              the sales-report to process
  -t, --transaction-report <TRANSACTION_REPORT>  the transaction-report to process
  -h, --help                                     Print help
  -V, --version                                  Print version

It produces a file named e.g. intermediate_202305_20230603142215.csv, where 202305 is the processed month with the timestamp indicating when the process was executed (03. June 2023 14:22:15).

Manual redaction of existing transactions

The last four columns of the file are pre-filled using sensible heuristics. The derived values may or may not be correct though and can be redacted. If modified, ensure certain constraints are met, otherwise further processing will fail during the export step.

The four columns that may be modified are:

Adding artificial transactions for Cash payments that were not entered into SumUp

It is also possible to add lines to capture transactions that were not entered into the SumUp System.

If you do so, ensure some fields are filled correctly, and some fields are left blank. I.e.

Constraints for manual redactions

For all items (existing SumUp transactions and artificially added transactions), it must be true that:

The export step

The lola-sumup export command:

Consumes the (potentially redacted) intermediate file and exports to different special purpose CSV files

Usage: lola-sumup export <INTERMEDIATE_FILE>

Arguments:
  <INTERMEDIATE_FILE>  the intermediate file to process

Options:
  -h, --help     Print help
  -V, --version  Print version

It produces three exports (with month and execution timestamp accordingly):

Description of the exports

Summary Report

The summary file collects all original and derived columns that are required to build the other reports or for deeper insights.

The columns of the resulting summary file are defined as follows:

Mittagstisch Report

The purpose of the Mittagstisch export is to provide the relevant financial information to the Mittagstisch team.

The columns of the resulting file are defined as follows:

Accounting Report

The purpose of the accounting export is to provide the relevant information on monthly level to the book-keeper.

The columns of the resulting accounting.csv file are defined as follows:

The first three columns after the date do not require postings. They serve for consolidation purposes:

Where the absolute net sum for the transitory accounts must not be > 0.05, i.e.: