balancing the USBank and OpenCollective accounts
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
forest 64e2de8abb Merge remote-tracking branch 'origin/main' into main 2 months ago
.gitignore first commit 7 months ago Update '' 6 months ago
getOpenCollectiveTransactions.js 😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤 6 months ago
index.js 😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤😤 6 months ago
manual-matchups.csv update August 2023 2 months ago
package-lock.json get missing OpenCollective transaction information w/ GraphQL 6 months ago
package.json get missing OpenCollective transaction information w/ GraphQL 6 months ago
screenshot.png update screenshot 6 months ago

high tech automated bean counting system

This is a commandline application that processes our USBank transaction export and our OpenCollective data in order to produce a single combined CSV file that shows how all the OpenCollective transactions match up with the UsBank transactions.

This helps our OpenCollective remain tethered to reality, its basically a way of auditing our finances to make sure that nothing is seriously wrong.

Overview of how to use it

  1. run the application following the "how to run it" steps below
  2. open theoutput.csv file with LibreOffice or Excel or whatever and look for discrepancies (marked with @@@@@@@@@)
  3. Have the OpenCollective admin make changes to the OpenCollective transactions to fix these discrepancies
  • "refund" OpenCollective transactions (this is the OpenCollective term for "remove" or "delete" a transaction record)
  • create new OpenCollective transactions (submit expense or add funds, under the actions menu)
  • Note that the system currently refuses to match up transactions which are more than 1 week apart... if the dates get messed up with corresponding txns more than 1 week apart for whatever reason then you can fix it by manually adding the data to the manual-matchups.csv file.
  1. re-run the application and reload the output.csv in your spreadsheet program
  2. repeat until the records are cleaned up to your satisfaction

output looks like this

set up OpenCollective API access

The Personal Token give you access to the API and is automatically authenticating you as an individual. To create one, go to your personal settings and navigate to the For developers section

For example you would go to:

Then set your token as an environment variable like OPENCOLLECTIVE_TOKEN=52ab4......

how to run it

  1. npm install
  2. mkdir input
  3. download the opencollective transaction export. it should be named layerzero-transactions.csv. put it in the input folder now the app pulls direct from OpenCollective using GraphQL API.
  4. download the usbank transactions export for, starting from 2022-07-24 until today. it should be named something like Checking - 9979_07-24-2022_03-21-2023.csv. put it in the input folder
  5. OPENCOLLECTIVE_TOKEN=52ab4...... node index.js > output.csv