1 unstable release
0.1.0 | Aug 1, 2024 |
---|
#32 in Finance
140KB
3K
SLoC
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:
Topic
: The main topic of the transaction, one ofMiTi
: Items sold by Mittagstisch. Automatically assigned if the transaction occurred before 14:15.Cafe
: Items sold by LoLa Café. Automatically assigned if the transaction occurred between 14:15 and 18:00.Verm
: Items sold by Renters of the rooms. Automatically assigned if the transaction occurred after 18:00.SoFe
: Items sold in context of the summer party ("Sommer-Fest").Deposit
: Key deposit.Rental
: Rental fee.Culture
: Items sold in context of cultural events.PaidOut
: Income paid out in cash to external party.Packaging
: Sold re-usable packaging for dishes
Owner
: Only relevant for TopicMiTi
:MiTi
(for menus produced and sold by Mittagstisch) orLoLa
(LoLa beverages and food from LoLa, sold by Mittagstisch)Purpose
:Consumption
orTip
(the former is also used for TopicsDeposit
,Rental
,Culture
, orPaidOut
)Comment
: Empty, can be manually filled to keep some context
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.
Account
: Provide an email address that clearly identifies who created the "artificial" transactionDate
Time
: Best guessType
: "Sales"Transaction ID
: Leave blankReceipt Number
: Leave blank - unless you do have some receiptPayment Method
:Cash
(asCard
would never be missing in the SumUp Transactions)Quantity
: Best guessDescription
: Best effort - ideally copy one of the existing descriptions to be preciseCurrency
: "CHF"Price (Gross)
: The paid amountPrice (Net)
: Copy of the previous value inPrice (Gross)
Tax
: Leave blankTax rate
: Leave blankTransaction refunded
: Leave blankCommission
: 0 (it's a cash payment)Topic
: See list of Topics aboveOwner
: Blank if Topic is notMiTi
.MiTi
orLoLa
if topic isMiTi
Purpose
: LikelyConsumption
unless it's a tipComment
: Provide some reference for later audit as to why the transaction is artificially created (e.g. reference to email)
Constraints for manual redactions
For all items (existing SumUp transactions and artificially added transactions), it must be true that:
Topic
andPurpose
contain valid valuesOwner
contains either valid values or is empty- for
Topic
MiTi
:Owner
must be eitherMiTi
orLoLa
- for
Topic
other thanMiTi
:Owner
must be blank
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):
accounting_202305_20230603142503.csv
mittagstisch_202305_20230603142503.csv
summary_202305_20230603142503.csv
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:
- Generic Columns:
Date
: Calendar Date
- Gross Values of consumptions of topics
MiTi
,Cafe
,Verm
(commissions not subtracted) split by payment methodMiTi_Cash
: Gross Cash Income Mittagstisch (including LoLa beverages)MiTi_Card
: Gross Card Income Mittagstisch (including LoLa beverages)MiTi Total
: Total Gross Income Mittagstisch (including LoLa beverages) [MiTi_Cash
+MiTi_Card
], also [Gross MiTi (MiTi)
+Gross MiTi (LoLa)
]Cafe_Cash
: Gross Cash Income CaféCafe_Card
: Gross Card Income CaféCafe Total
: Total Gross Income Café [Cafe_Cash
+Cafe_Card
]Verm_Cash
: Gross Cash Income RentalsVerm_Card
: Gross Card Income RentalsVerm Total
: Total Gross Income Rentals [Verm_Cash
+Verm_Card
]SoFe_Cash
: Gross Cash summer partySoFe_Card
: Gross Card summer partySoFe Total
: Total Gross summer party [SoFe_Cash
+SoFe_Card
]Deposit_Cash
: Gross Cash Key DepositDeposit_Card
: Gross Card Key DepositDeposit Total
: Total Gross Key Deposit [Deposit_Cash
+Deposit_Card
]Packaging_Cash
: Gross Cash Key DepositPackaging_Card
: Gross Card Key DepositPackaging Total
: Total Gross Key Deposit [Packaging_Cash
+Packaging_Card
]Rental_Cash
: Gross Cash Rental PaymentRental_Card
: Gross Card Rental PaymentRental Total
: Total Gross Rental Payment [Rental_Cash
+Rental_Card
]Culture_Cash
: Gross Cash CultureCulture_Card
: Gross Card CultureCulture Total
: Total Gross Culture [Culture_Cash
+Culture_Card
]PaidOut_Cash
: Gross Cash PaidOutPaidOut_Card
: Gross Card PaidOutPaidOut Total
: Total Gross PaidOut [PaidOut_Cash
+PaidOut_Card
]
- Gross values consumption, Tips and total reported values by payment method:
Gross Cash
: Total Gross Income Cash [MiTi_Cash
+Cafe_Cash
+Verm_Cash
+SoFe_Cash
+Deposit_Cash
+Packaging_Cash
+Rental_Cash
+Culture_Cash
+PaidOut_Cash
]Tips_Cash
: Tips CashSumUp Cash
: Total Income Cash [Gross Cash
+Tips_Cash
]Gross Card
: Gross Gross Income Card [MiTi_Card
+Cafe_Card
+Verm_Card
+SoFe_Card
+Deposit_Card
+Packaging_Card
+Rental_Card
+Culture_Card
+PaidOut_Card
]Tips_Card
: Tips CardSumUp Card
: Total Gross Income Card [Gross Card
+Tips_Card
]Gross Total
: Gross Total Income [Gross Cash
+Gross Card
]Tips Total
: [Tips_Cash
+Tips_Card
]SumUp Total
: [Gross Total
+Tips Total
] or [SumUp Cash
+SumUp Card
]
- Card related payments: Gross values, commission and net values by topic:
Gross Card MiTi
: Gross Card Income MittagstischMiTi_Card
(including beverages LoLa)MiTi_Commission
: Card Commission for Mittagstisch (Menus and Tips, but not from LoLa beverages)Net Card MiTi
: Net Card Income Mittagstisch [Gross Card MiTi
-MiTi_Commission
] - commission on meals and tips are deducted, sales of beverages still includedGross Card LoLa
: Gross Card Income LoLa (Café, Vermietungen, summer party, Deposit, Rental, Culture) [Cafe_Card
+Verm_Card
+SoFe_Card
+Deposit_Card
+Rental_Card
+Culture_Card
+PaidOut_Card
]LoLa_Commission
: Card Commission for LoLa (non-Mittagstisch related, but including commission for items sold by MiTi)LoLa_Commission_MiTi
: Card Commission for LoLa items sold by MiTi only, so not from Café or RentalsNet Card LoLa
: Net Card Income LoLa (Café and Vermietungen) [Gross Card LoLa
-LoLa_Commission
]Gross Card Total
: Gross Card Income (MiTi, Café, Vermietungen) [Gross Card MiTi
+Gross Card LoLa
]Total Commission
: Card Commission Total (MiTi, Café, Vermietungen) [MiTi_Commission
+LoLa_Commission
]Net Card Total
: Total Net Card Income [Gross Card Total
-Total Commission
]Net Payment SumUp MiTi
: Total Net Payment SumUp Mittagstisch related (Net card payments concerning Mittagstisch (Sales from meals, tips, sales of LoLa goods paid via card)) [MiTi_Card
+MiTi_Tips_Card
-MiTi_Total_Commission
]
- Tips by Topic:
MiTi_Tips_Cash
: Tips for Mittagstisch paid in CashMiTi_Tips_Card
: Tips for Mittagstisch paid by CardMiTi_Tips
: Tips for Mittagstisch (Total) [MiTi_Tips_Cash
+MiTi_Tips_Card
]Cafe_Tips
: Tips for CaféVerm_Tips
: Tips for Vermietungen
- Split of Topic Mittagstisch by Owner:
Gross MiTi (MiTi)
: Gross Income Mittagstisch from their own Menus (paid via Card or Cash)Gross MiTi (LoLa)
: Gross Income Mittagstisch with LoLa-items (Beverages...) (paid via Card or Cash)Gross MiTi (MiTi) Card
: Gross Income Mittagstisch from their own Menus (paid via Card only) (not including tips)Net MiTi (MiTi) Card
: Net Income Mittagstisch Menus w/o commission paid by card [Gross MiTi (MiTi) Card
-MiTi_Commission
]Net MiTi (LoLa)
: Net total income Mittagstisch with LoLa items w/o commission [Gross MiTi (LoLa)
-LoLa_Commission_MiTi
]Contribution MiTi
: Share MiTi from selling LoLa items [20% *Net MiTi (LoLa)
]Net MiTi (LoLA) - Share LoLa
: 80% of Net total income Mittagstisch with LoLa items w/o commission [Net MiTi (LoLa)
* 0.8]Sponsored Reductions
: Reductions sponsored by LoLa [2 * (MealCount_Reduced
+MealCount_Regular
)]Debt to MiTi
: Net amount LoLa needs to pay out to Mittagstisch [Net Payment SumUp MiTi
-Net MiTi (LoLA) - Share LoLa
+Sponsored Reductions
]Income LoLa MiTi
: Income LoLa from MiTi selling LoLa [Gross MiTi (LoLa)
-Contribution MiTi
]
- Statistics relevant for Mittagstisch:
MealCount_Regular
: Number of regular meals per dayMealCount_Reduced
: Number of meals with reductions sponsored by LoLa per dayMealCount_Regular
: Number of meals for stagaire sponsored by LoLA per dayMealCount_Children
: Number of children meals per day
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:
- Generic columns
Datum
:Date
- Count of Menus
Hauptgang
: Number of regular meals per dayMealCount_Regular
Reduziert
: Number of regular meals per day [MealCount_Reduced
+MealCount_Praktikum
]Kind
: Number of children meals per dayMealCount_Children
- Income by ownership (MiTi or LoLa):
Küche
: Gross income from menus [Gross MiTi (MiTi)
]Total Bar
: Gross income from selling LoLa items (Bar) [Gross MiTi (LoLa)
]Anteil LoLa
: Gross income LoLa from MiTi selling LoLa itemsAnteil MiTi
: Gross income MiTi from selling LoLa items
- Gross totals by payment method, differentiating income and tips:
Einnahmen barz.
: Income and Tips paid in Cash [MiTi_Cash
+MiTi_Tips_Cash
]davon TG barz.
: Total Tips paid in cashMiTi_Tips_Cash
Einnahmen Karte
: Income and Tips paid by Card [MiTi_Card
+MiTi_Tips_Card
]davon TG Karte
: Total Tips paid by cardMiTi_Tips_Card
Total Einnahmen (oT)
: Total payments (Cash + Card - w/o Tips) [MiTi Total
]
- Net totals:
Kommission Bar
: Commission for LoLa paymentsLoLa_Commission_MiTi
Netto Bar
: Net Income for selling LoLa items [Total Bar
-Kommission Bar
]Karte MiTi
: Gross income from menus paid by card (not including tips) [Gross MiTi (MiTi) Card
]Kommission MiTi
: Commission onGross Card MiTi
(including commission on tips)MiTi_Commission
Netto Karte MiTi
: Net income from menus paid by card [Net MiTi (MiTi) Card
]
- Netting with LoLa
Net Total Karte
: Net card payments concerning Mittagstisch (Sales from meals, tips, sales of LoLa goods paid via card) [Net Paymnet SumUp MiTi
]Verkauf LoLa (80%)
80% of net sales lola goods [-Net MiTi (LoLA) - Share LoLa
]Gesponsort
LoLa sponsored reductions (2.00 per Meal) [Sponsored Reductions
]Überweisung
: Net Payment LoLa to Mittagstisch [Debt to MiTi
]
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:
Date
:Date
Payment SumUp
: Total Net Income plus tips paid via Card. Daily payment by SumUp (next business day) [Net Card Total
+Tips_Card
]. Will be posted10110/10920
, but based on Account Statement, not this report.Total Cash Debit
: Total daily cash debit [Gross Cash
-MiTi_Cash
-PaidOut Total
]Total Card Debit
: Total daily card debit [Gross_Card_LoLa
+Tips_Card
-MiTi_Tips_Card
]10000/23050
: Total Cash Income Key DepositDeposit_Cash
10000/30200
: Total Cash Income CafeCafe_Cash
10000/30700
: Total Cash Income Food RentalsVerm_Cash
(LoLa Food sold by renters)10000/30800
: Total Cash Income summer partySoFe_Cash
(LoLa Food sold during summer party)10000/31000
: Total Cash Income Rental FeeRental_Cash
(fees for renting the rooms)10000/32000
: Total Cash Income Cultural PaymentsCulture_Cash
10000/46000
: Total Cash Cost Reduction on Material CostPackaging_Cash
10920/10000
: Total Gross Payments Card paid out in cash to external partiesPaidOut_Card
10920/23050
: Total Gross Payments Card Key DepositDeposit_Card
10920/30200
: Total Gross Payments Card CafeCafe_Card
10920/30700
: Total Gross Payments Card RentalsVerm_Card
(LoLa Food sold by renters)10920/30800
: Total Gross Payments Card summer partySoFe_Card
(LoLa Food sold during summer party)10920/31000
: Total Gross Payments Card Rental FeeRental_Card
(fees for renting the rooms)10920/32000
: Total Gross Payments Card Cultural PaymentsCulture_Card
10920/46000
: Total Gross Payments Card Reduction on Material CostPackaging_Card
10920/20051
: Net Card income + tips (card) Mittagstisch [Net Card MiTi
+MiTi_Tips_Card
]10920/10910
: Tips LoLa paid via Card [Tips_Card
-MiTi_Tips_Card
]68450/10920
: Commission for Café, Vermietung, summer party, Deposit, Rental, Cultural Payments, andPaidOut
, i.e. w/o Mittagstisch [Commission LoLa
]59991/20051
: LoLa sponsored reductions (Sponsored Reductions
)20051/10930
: Amount LoLa owes to Mittagstisch (Debt to MiTi
)20051/30500
: Income LoLa from MiTi selling LoLa [Gross MiTi (LoLa)
-Contribution MiTi
=Income LoLa MiTi
]10930/10100
: Payment to Mittagstisch (Debt to MiTi
or20051/10930
) - posting date according to the actual payment
The first three columns after the date do not require postings. They serve for consolidation purposes:
Payment SumUp
has to match the bank statement showing the daily sumup payments (which are posted on our account with a delay of one or more days).Total Cash Debit
andTotal Card Debit
helps to reconcile the cash account ledger ("Kassendokument"). Please note that those two columns potentially aggregate multiple entries in the cash account ledger for each day.
Where the absolute net sum for the transitory accounts must not be > 0.05, i.e.:
- for
10920
: abs(10920/30200
+10920/30700
+10920/30800
+10920/23050
+10920/46000
+10920/31000
+10920/32000
+10920/20051
+10920/10000
+10920/10910
-Payment SumUp
-68450/10920
) < 0.05 - for
20051
: abs(10920/20051
-20051/10930
-20051/30200
+59991/20051
) < 0.05
Dependencies
~30–59MB
~1M SLoC