Integration project - open collective and bookkeeping

Figma board for jamming

https://www.figma.com/file/gsS5SPC1trt3jvio1WZJ4G/OC%2FWise%2FFortnox-integration-project?type=whiteboard&node-id=0%3A1&t=u0xwuFQFmsxRZJTP-1

notes from conversation with Christian Ubbeson

Current situation:

• The process is working as is, only thing that is not perfectly in line with bookeeping needs is the lump registration of all reciets from all dreams, something like 900 reports and often multiple reciepts per report
• there is also no ongoing bookkeeping but all bookkeeping is done in the fall where the dreams reciepts are all lumped together into one big booking and then all of the 50 or so transactions for infrastructure that have accumulated over the year are tagged and entered into fort knox
• There are currently accounts used in the bookkeeping for around areas like: power/electricity, machines. Dreams is one account for the one big entry (these are indicated by the tagging of the transactions that go into the bookkeeping)
• using tags directly from making more projects for each budget area (like power, water etc) in open collective can be somewhat sketchy because there is no way for an admin to move erroneous listings from one project to another
• the bookkeeping entries are sent in september/october to a bookkeeping service that does the work, prepares the annual reports (gränslandet requires K2 reporting (some extra notes) due to the larger turnover than most associations)
• the materials sent for bookkeeping is one transaction record from the bank, and then a couple of exports from OC (dreams, infrastructure and org overheads) which are then first tagged properly by christian before being processed into bookkeeping.
• After reports are done in the fall, there is revision (by Lucas Lilja) of the report and books.

Considerations for the future:

• The organistation is looking to be more active year round, by doing things like making the dream process come earlier in the year, along with setting memberships and running the lottery earlier so that more large dreams can be accomplished
• There are is also the possibility of reinstating the Fund 51 for expenses that are related to borderland but that may fall outside of the event/budget allocations as set in the AP
• It would be useful, in Christians perspective, to be able to view how the year is progressing towards the budget during the year, now nothing can really be seen but can only be hunched until the bookkeeping is done at the end of the year. Therefore running bookkeeping would be useful so that the reports are easliy accessible, at least quarterly

Other considerations:

• The current costs for bookkeeping are pretty low, around 30K per year, due to the batch nature of the work, changing to an ongoing bookkeeping practice that would need much more ongoing work could strongly increase that cost which is undesireable, any solution should make the entry work minimal in order to bring benefit to the organisation

there are two main reasons for doing the work:

1. transparency (getting ) records from bank/bookkeeping visible in open collective
2. bookkeeping simplicity, eliminating work around tagging and entry from OC to Fort Knox (and possibly squaring the process with the more right one transaction one bookkeeping record requirement) as well as providing ongoing reporting

Christian is mostly interested in number 2, synching from OC to bookkeeping as most people are not looking so much into the state of the economy of the association on an ongoing basis.

opened questions:

• what do the APIs of Fort Knox allow for? (posting "verifikat" works fine)
• should there be a 1-1 link between projects on open collective and bookkeeping accounts?
  • is there a need for things like booking machines on an account over all in order to do yearly write-offs? right now everything is just written off as costs each year. In that case can there be both tagging as "machine" and "borderland build" as project or something like that?
• how is each dream set up on OC? (answer: there are no individual dreams set up on OC, just a project called dreams 2022 etc.)
• can the transaction/reimbursment when sent by Wise from OC be directly sent into the bookkeeping records with the reciepts from OC? What is the Wise api like? (Looks like we are going to run sheduled batch processess and not have them triggered by wise api/webhook)
• is there a feature request to OC for admins to move a paid request from one project to another in case of mishaps?

resources

Fort knox API: https://apps.fortnox.se/apidocs
Rate limits
The limit per access-token is 25 requests per 5 seconds. This equals to 300 requests per minute.

Links from Christian

Regarding legal requirements on book-keeping and accounting, here's a good guide: https://www.bfn.se/wp-content/uploads/2020/06/bokforingsskyldig-ideell-kons.pdf

And here's the final result after putting it together in the annual financial statement: https://docs.google.com/document/d/10d5jJVwn1Bmr47E3yn6Viz9vKnP0I_sCVfJvmeugVN0/edit#heading=h.jy0trka6pa0e

https://drive.google.com/file/d/1pkWdpPPi336PCMh02xq6EXErvE0iCBQb/view?usp=sharing P&L report

Qs on Fortnox integration

• do we need to register app with Fortnox in order to get access tokens to the API? https://www.fortnox.se/developer/authorization/get-authorization-code (Viktor has registered as Dev to recieve api key, unclear still how to authenticate without using browser)

• Are supplier invoices resonable for both incoming invoices and all expenses? https://apps.fortnox.se/apidocs#tag/SupplierInvoiceAccrualsResource
  (Nope, we use Vouchers (verifikat) for everything)

• is there a way to use the API without having to authorize through a UI? So that there can be an authorized agent/server just entering the bookings? there is a server setting in fortnox api for server-to-server integrations.. https://www.fortnox.se/developer/authorization
  (There has to be one initial verification in order to get the authcode with Oauth, then it can be refreshed within 30 days consecutively so that no more human authentications are needed)

• what are the max size limits for files in the OC reinbursments/invoices? fortnox has a default 5gb storage where files can be reached at paths: https://www.fortnox.se/developer/guides-and-good-to-know/best-practices/vouchers 1500 reciepts with 3MB photos would take up all the space..(Open collective hosts the reciept photos openly, we can just link to there)

ways forward

Batch process:

1. create a batch program that checks the open collective and wise transfers once per day or something like that
2. logs whenever it finds a new transaction
3. gets the transaction and payment information from Wise/Open Collective
4. Posts an invoice and a voucher to the proper accounts on Fortnox

batch process

information needed in order to book in fortnox.

• Information to create invoice, likely to be:
  • amount payable, accounts to be booked at (debit (WISE) & credit sides (Infrastructure)), bookkeeping period
• information to create voucher (verifikat)
  • reciept/image

Create supplier invoice tar emot:

{
"SupplierInvoice": {
"@url": "string",
"AdministrationFee": "string",
"Balance": "string",
"Booked": true,
"Cancelled": true,
"Comments": "string",
"CostCenter": "string",
"Credit": true,
"CreditReference": 0,
"Currency": "string",
"CurrencyRate": "string",
"CurrencyUnit": 0,
"DisablePaymentFile": true,
"DueDate": "2019-08-24",
"ExternalInvoiceNumber": "string",
"ExternalInvoiceSeries": "string",
"Freight": "string",
"GivenNumber": "string",
"InvoiceDate": "2019-08-24",
"InvoiceNumber": "string",
"OCR": "string",
"OurReference": "string",
"PaymentPending": true,
"Project": "string",
"RoundOffValue": "string",
"SupplierInvoiceRows": [],
"SupplierNumber": "string",
"SupplierName": "string",
"Total": "string",
"VAT": "string",
"YourReference": "string",
"VoucherNumber": 0,
"VoucherSeries": "string",
"VoucherYear": 0,
"VATType": "NORMAL",
"SalesType": "STOCK",
"AccountingMethod": "ACCRUAL",
"Vouchers": [],
"FinalPayDate": "2019-08-24"
}
}

Create voucher

{
"Voucher": {
"@url": "string",
"Comments": "string",
"CostCenter": "string",
"Description": "string",
"Project": "string",
"ReferenceNumber": "string",
"ReferenceType": "INVOICE",
"TransactionDate": "2019-08-24",
"VoucherNumber": 0,
"VoucherRows": [],
"VoucherSeries": "string",
"Year": 0,
"ApprovalState": 0
}
}

retrieving wise transactions

Question: Does the trasferwise transaction have any identifying information containing the origin opencollective expense/invoice so that reciepts/project data can be retrieved from there?

authentication looks similar to fortnox..: https://docs.wise.com/api-docs/guides/open-banking

GET /v3/spend/profiles/{{profileId}}/cards/{{cardToken}}/transactions?pageSize=10&pageNumber=1&fromTransactionDate=2022-11-01&toTransactionDate=2022-11-15
example get from wise that returns a list of transactions

transaction ID can be stored as identifier, we could create boolean flag inBookkeeping to see if transaction has been processed

In order to book transaction, transaction id, createdDate, amount and currency should be sufficient.
"id": "342671",
"createdDate": "2022-11-28T08:17:54.241236Z",
"transactionAmount": {
"amount": 1.5,
"currency": "SGD"
},

retrieving project tags and reciepts from OC

authentication: https://docs.opencollective.com/help/developers/oauth

graphql API docs: https://graphql-docs-v2.opencollective.com/

Current questions:

testing/safety issues

• Should the script save the expenses that are to be inserted into fortnox as a csv file or as a json file? things like :;, in expense descriptions/project names would be better handled in json files I suppose…
• Should the script also handle invoices in "the wrong place", something like a dream has gotten an invoice type expense approved in the dreams project, should that be added like any other expense? probably not?

Avstämning med Chritian - 2023-10-04

• progress: Viktor has code that can:
  • Call open collective and get transaction information and URLs for transactions, and able to read what the subcollectives of the borderland collective is in order to get a list of all transactions
  • Call the Wise API to get transactions
  • Call the Fortnox API and create vouchers (verifikat) with Date, Amount (debit/credit), Accounts, Description and URL (URL link to reciept will be saved as a comment on the vouchers, multiple files will be inserted comma-separated)

Blockers cleared:

• I got access to read information from Wise, should be able to use my personal token for getting the info from there, should change for production
• We set up an app (Fortnox Integration) at the Borderland collective in order for me to be able to read from OC. Christian created the authcode which Viktor can use until revoked (Viktor will check with OC when that happens)

Other notes:

• We discussed how to do the mapping between projects/tags and fortnox accounts. We talked about one possible implementation where the Project (like Dreams 2023) could include the account number in the name or description so that we can do the matching through that. Tags could include the account number like "5410 Radios" which would make it easy for the script to know which account to post to.

• Another approach is to have a matching table that is manually updated with "tag/project x = fortnox accounty y"

• We talked about what to do in the event that we don't know what fortnox account the expense should be booked at (wrong tag/project not in fortnox yet/other..). Best solution for this would be to recieve information (email?) that there are unprocessed bookings and to allow a user to log in to a screen where a list of the transactions that are uncategorised are shown, along with a list of the available accounts in fortnox (read from API) and that the user chooses and saves, then the transactions are processed. When tags/projects are updated to match the fortnox accounts, further expenses from there should go automatically.

• Christian is ok with having automated bookkeeping when there is a match on accounts, so long as we make sure that we only do actually completed wise transactions.

• We talked about where the code is going to be executed? (it might need access also to mail-sending for errors and things that need clearing up)

  • AWS or digital ocean?
  • Should this be a deeper integration with OC and it be hosted along with their software as "addon" and an official integration with Fortnox eventually? Is this even in OCs interest as it is only relevant for hosts (self-hosted in this case)

• Figured out that the "transfer number" on the Wise side corralates to the "mechantID" on the OC side for a transaction, we should be able to use this to do the matching to get the expense info.

• Found that there can sometimes be an issue with transactions not going through due to the banks not accepting the transfer on the other end. We should be able to use the "Status" tag on the wise transfer to only book the transactions that are marked "COMPLETED"

• As bookkeeping is done now in October, Christian will send all of the information to Viktor who can then try to replicate the books of gränslandet on a fortnox test account by manually adding the bank-transfers and doing all the wise stuff with the scripts.

Issues:

• If an expense has multiple reciepts, a comment field for a voucher (verifikat) only allows for 1000 characters. Normally a link is around 200 chars, but if there are really long filenames and many reciepts, this would become a problem. Update: it is definitly a problem since expenses are incouraged to have more than one receipt for lower transfer fees. A possible solution is that all the links are collected for each transaction and made into a pdf that is uploaded along with the transaction to fortnox as the "reciept index" for that transaction

• It may not be possible to just query for the merchantId only, in that case it may be useful to have the batch process download all transactions from wise since last run, and also download all expenses from all projects on opencollective since last run. Then have those limited lists that can be iterated over to get the information needed for insertion into fortnox.

• The other approach would be to create queries one-by-one for the transactions from wise and that the scripts issue queries that iterate through the "accounts" (projects) on open collective until they find the right transaction to get the info from. seems like a slower option.

• only status object on Wise we can use is status in the trasactions, this is set to outgoing_payment_sent when a payment is made, however, it can revert if the payment bounces, so if we want to make sure to only book non-bounced payments (about 1-5payments bounced last year), we would have to wait a sufficient time (2-3 bank days would be sufficient for majority of cases). this would have auto-bookkeeping trailing a few days behind when script is run..

Meeting notes from meeting with Hugi

• Put files into fortnox for each transaction (pdf with eventId and html link to be able to access during revision, plus reciept links), if possible also store a .json file (or txt file if not allowed) to be able to programmatically work with voucher info
• Use digital ocean, could use functions but should probably be a droplet
• don't do any database type stuff just have a script that runs and avoid the UI stuff
• do a matching table for the fortnox accounts and the projects/tags, should be very easy and there is work on the OC side to be able to configure accounting "accounts" on OC that you can choose between when you add expenses/invoices on the platform, will be online within a year.
• check with Christian if it is ok with a "slask" account, that unmatched transactions will go to, and then manual handling of new transactions are to be done from them. Hugi strongly wants to avoid having to do the intermediate database and UI soluition as this has a bunch of security and maintenance ramifications.
• use postmark for error emails
  • for the inconsistency problem of having transaction statuses go from outgoing_payment_sent to bounced_back is to

Next steps:

• Keep getting scripts ready, now with read-access to real data from wise & open collective.
• Test recreation of books on test fortnox
• create .env files with keys etc.
• Look into email notifications in case of errors and when there are un-resolved entries, emails:
  • X was run, these are now booked (too much?)
  • There was an error running the script
  • There are transactions which need fortnox account specifications

links

sample functions:
https://github.com/digitalocean/sample-functions-python-sendgrid-email/tree/main
https://github.com/digitalocean/sample-functions-python-helloworld/tree/master/packages/sample/hello