brafdlog/caspion

Automated budget tracking from Israeli financial institutions

Caspion - Automated expense tracking from Israeli banks and credit cards

Many people track their expenses with spreadsheets or with budgeting apps (like YNAB).

The most annoying part of this process is transferring the data from the banks or credit cards to the budgeting tool you use. It usually requires you to go to the website of each one of your banks and credit cards, and manually copy the data to your budgeting tool.

This project aims to automate this process by fetching the data automatically from your Israeli financial institutions and sending it to the budgeting tool of your choice.

In addition, it can automatically set an expense category for transactions according to predefined patterns.

Internally it uses the Israeli bank scrapers npm package.

If you want to ask questions, suggest features or report issues, join our discord channel.

Note

We are in transition to a new UI. Some features don't yet exist in the new UI and will be added overtime. You can still access the old UI by clicking on the switch next to ממשק חדש.

Features

• One click to fetch transactions from multiple Israeli banks and credit cards
• Exporting transactions to Google Sheets, YNAB, Excel (CSV) or JSON file
• A basic UI for configuration

Running the app

Download the latest version from Releases page, or build it from source, with the instructions below. (Mac users, you may follow these instructions).

Initial setup

The first time you run the app, you will need to set up the accounts you want to fetch data from (importers).

Now you can set up the exporters - where the data will be sent to. The CSV exporter is enabled by default. If you want to export to YNAB, see instructions at the end of the README.

Report a problem

We are still in beta, and you may find errors. Please use the REPORT A PROBLEM button in the app to report to us.

Use this button to find the logs folder as well.

Running in a development environment

Prerequisites

• NodeJS.

• node-gyp. In Windows you have a checkbox in the NodeJS installer to install also the node-gyp.
  This module is for packages that compile native code in their installation, in our case it is required for electron and keytar. (Note that if you install visual studio, you need to add the workload of desktop development with C++)

• Yarn.

Linux Prerequisites

Currently, this project depends on libsecret, so you may need to install it before running yarn.

Depending on your distribution, you will need to run the following command:

• Debian/Ubuntu: sudo apt-get install libsecret-1-dev
• Red Hat-based: sudo yum install libsecret-devel
• Arch Linux: sudo pacman -S libsecret

Mac Prerequisites

For M1 macs, if you get an error while scraping due to not being able to download chromium, download it manually as described here. Then in the settings, set the chromium path to be the result of which chromium

Start from Source

• Run yarn to install the dependencies
• Run yarn serve to start the app
• Set up additional exporters (Optional)
  • If you want to set up YNAB or Google Sheets, see instructions below
• Run by clicking on the Run button in the app
• Configure automatic category classification (Optional)
  • Open categoryCalculationScript.js. This file contains the patterns for classifying transactions to categories automatically.
  • Edit this file to add any mapping from function description to category that fits your needs.
  • If using YNAB, the categories you return must match category names in YNAB

Building for production

• Run yarn build

YNAB integration setup (optional)

YNAB is a budgeting software. If you want to manage your budget there and have your expenses updated automatically to YNAB follow these steps:

• Create an account in YNAB
• Create in YNAB unlinked accounts for each financial account you want to track (bank accounts and credit cards)
• Get the YNAB access token
  • In YNAB go to Account settings -> Developer settings
  • Click on New Token and generate
  • On the top of the screen you will see the full token (the token with XXXX in it is not the full one).
  • Save this token in the YNAB settings in the app.
• Choose your budget from the dropdown
• Fill the table containing account number to ynab account id mapping
  • For each account you want to track add another row to the table
  • The account number could be the credit card number, or the bank account number. To be sure, you can run the app so it exports transactions to a CSV and get the account number from there.
  • Then choose the matching a ynab account from the dropdown
• Click on Save to save the configuration

Google Sheets integration setup - when building from source code (optional)

If you set Google Sheets as an exporter the transactions results will appear in a dedicated sheet. In the release version of Caspion, this integration will work out of the box. If you wish though to build/debug this from source, you will need to setup your own test OAuth 2.0 Client on GCP (Google Cloud Platform). Follow these steps:

• Create a new project in GCP
• Enable Google Sheets API
• On the left panel, click to create OAuth consent screen
• Now you are ready to create your credentials that will be used later on by Caspion
  • On the left panel, click "Credentials" -> "CREATE CREDENTIALS" (on the top) -> "OAuth 2.0 Client IDs"
  • Application Type: "Web Application"
  • Authorized JavaScript origins, add: http://localhost
  • Authorized redirect URIs, add: http://127.0.0.1:42813/callback and/or http://localhost:42813/callback
  • Save.
  • Write down the generated Client ID & Client Secret

Now that you have the keys above, store them as env variables:

EXPORT GOOGLE_CLIENT_ID=XXXXX
EXPORT GOOGLE_CLIENT_SECRET=YYYYYY

That's it. Next time you'll run the code a one time OAuth authorization window will appear and ask for access.

CSV (Excel) Note

In some cases you may get gibberish when you open the CSV file in Excel. In this case, please follow the next steps:

1. Open a new Excel document.
2. Go to Data -> Import From CSV/Text.
3. Select your CSV file.
4. For the "Encoding"/"File Origin", select Unicode UTF-8 (65001).

Release

The Release workflow, configured in .github/workflows/release.yml, will draft a new release or update the artifacts on an existing draft, according to the following conditions:

1. A change pushed to the master branch.
2. The version from the package.json, with the prefix v, is not a published release.
3. Upload the artifacts to the v${package_version} Github Release.

It means that after you published a new release, from the Github interface, you need to upgrade the version in the package.json file, to get a new draft.

Disclaimer

Providing your financial account credentials to software is not risk free. We will do our best to protect your credentials, but we take no responsibility for any possible damages. If you want to use this we suggest you ask your financial institution for credentials for a user that has only read access to the relevant account and use those credentials to reduce the potential risk.

caspion's Issues

Exporting data to a Google Spreadsheet to allow automations

Exporting data to a Google Spreadsheet to allow automations - can be exported on demand (and be filtered by dates and/or cards/categories) AND on a regular basis, when new transaction appears. This will provide an opportunity for further automations, such as emails.

Take category classification logic from a json instead of code and expose it in the configuration screen

Currently the logic for classifying a transaction relies on the categoryCalculationScript.js.
Most of the logic there is matching patterns of descriptions to patterns or exact matches.
In order to make this easily configurable by the user, we want to move this logic to a json configuration that will contain the list of patterns and exact matches.
After this is taken from the json, expose a ui for managing this in the config screen.

Notes:

1. We need to allow people to have their own setup - both control category names and control the mapping, because this is something that is not consistent across people and across budgeting tools. In the future we may consider having this configuration per output vendor.
2. We want this to work both in development from the repository and for the released electron app.
3. When running for the first time (either in dev or released electron app) should work with a default configuration without requiring an initial setup.
4. In future consider supporting regex patterns, but not required for initial phase.
5. Write unit tests for this logic.

Proposed solution:

The getCategoryNameByTransactionDescription will fetch this json and use it to get the transaction category according to the following logic:

Look for an exact match for the transaction description string in the exactMatches arrays.

If not found fall back to looking for substring matches in the patternMatches arrays.

JSON structure:

{
  "exactMatches": {
    "electricity": ["חברת חשמל", "חיוב חשמל חודשי"],
    "groceries": ["המכולת של יהונתן", "המכולת של ברוך"]
  },
  "patternMatches": {
    "car maintenance": ["מוסך", "טסט"],
    "groceries": ["מכולת"]
  }
}

This json should be saved in a place where it can be used both in development and in release. There are a few options to implement this, see open questions.

Have a defaultCategoryMapping.json file committed to git. If the user has no mapping logic defined yet, take the default and save it as the user's json for future customization.

Open questions:

1. Should this be part of the config or separate json?
2. Where should this be stored? Could be a json file or local storage etc. See: https://medium.com/cameron-nokes/how-to-store-user-data-in-electron-3ba6bf66bc1e
3. Need to verify it works nicely with Hebrew category names and patterns.

test

Merge with the israeli-bank-scrapers-desktop repo

Project for issues we want to handle pre merge: https://github.com/brafdlog/budget-tracking/projects/4

Related issues:

• Make the configManage use the crypto module and remove hard coded encryption key
• Make the node code expose an api as defined here
• Update the electron UI flow to work with two main flows - configure, run scraping as defined here
• Make the electron UI work the node "library" code
• Remove the Contributors part or hide it in a Menu or something #87
• Align filename #57

Post Merge Actions:

• Decide how to release (by Tag or by pushing to master)
• Copy relevant issues from israeli-bank-scrapers-desktop.
• New repo/app name?
• Check if there unused packages
• Add MacOS to tests
• Create Splash screen (until the renderer process loaded)
• Set show browser in configManager? Define it for all importers?
• Add ability to disable importer and exporter (and keep them configured)

Redesign the app

After we have a working MVP it would be a good time to stop and think what we want the UI/UX of the app to be.

Take into account:

• Configuring importers
  • YNAB setup
  • Google sheets setup
• Configuring exporters
• Global configuration (like show browser)
• Import and export progress indication
• Category management and rules for automatically assigning transactions to categories
• Report after imporn export finishes

Errors when "upgrading" the app and reading a config that is from an old version

When we are working with the config read and write, we are assuming that the loaded config is in the correct schema, because it saved by us or loaded from the default schema.

But if you adding a new Exporter, for example, it will cause an error since the exporter in the config is not nullable, but because it is a new exporter, it does not exist in the existed config.

Also, if you think the app is separated into two parts- one is managing the config, and the second is giving the config to the scraping process, you don't want the user will have to create a full config with all the exporters, just for marking all the exporters as enabled=false.

What I'm saying is that maybe all the config properties on all the levels should be nullable.

Limit imports from vue code to be only from the index of the originalBudgetTrackingApp

I'm trying to think about a way to force us to import only from originalBudgetTrackingApp and not from any originalBudgetTrackingApp/* when we are on the FrontEnd code.

Maybe this:
https://eslint.org/docs/rules/no-restricted-imports
Just keep in your mind

Originally posted by @baruchiro in #91 (comment)

Remove the Contributors part or hide it in a Menu

Splash Screen

Because we are using vuex-persist, and it loads the config asynchronicity, the app can't load until the store is loaded, so we have a Splash Screen (currently just a text "SPLASH").

We need to create more beautiful or just loading spinner, or use Vue Suspense feature (don't know if it supported in Vue2)

Pass only the relevant part of the config to internal functions

Pass sub-config to internal functions instead of the whole config. The functions should get only the relevant config, for example, the export function should get only the outputVendors part from the Config.

Organize the code

Rename the originalBudgetTrackingApp to library (or whatever) and move all Vue/Fronend related code to ui folder or whatever.

Then I think you can create another folder for tools like your YNAB helper. (It's not mean that this issue is assigned to @brafdlog)

Replace the existing advanced configuration json with ui components

The current json textarea was a quick and dirty solution to allow changing the config.
We want to allow users to change the configuration in a clear ui.
So, instead of the json text area we want to have ui components that allow setting all the configurations.

Show the progress of the import and export process

Create a ui that conveys clearly the current status of importing/exporting.

This will have two parts:

1. An accounts state in the ui code that gets updated by listening to events.
2. A component showing each account and its current status.

Add ui for configuring exporters

Authenticate with google sheets with oauth installed application flow instead of service flow

The current authentication with google sheets is through a service account.

This is far from ideal because it requires an annoying process and because it requires a scope (auth/spreadsheets) that grants much more access than what we actually need.

Instead we should implement the oauth installed application flow and probably use the auth/drive.file and create the spreadsheet from the app.

Also this should be exposed in the ui of the configuration so the user clicks a button to integrate google sheets and that starts the process.

Make the category classification logic learn and improve over time

The simplest implementation for this is to fetch periodically from YNAB/Google sheets the mapping from transaction description to category and save this locally. Then use this for classifying future transactions with the same description.

An alternative implementation is to expose in the ui the transactions that are about to sent to the vendor and allow the user to classify them inside our app. This will mean we can store the mapping of description to category without fetching it from outside.

Add the Log Level to the BudgetTrackingEvent itself

I think it would be cleaner to add the level to the BudgetTrackingEvent itself, setting it by default to INFO and having error set it to ERROR. Then you won't need this class and can use the event publisher directly.

In the current implementation you don't have access to the full api of the emitter which is useful (see comment about the option to set the status by listening to events)

Originally posted by @brafdlog in #110 (comment)

Show error when vuex-persist failed, and re-fetch the config

Make the google sheets exporter in the library code work with the oauth flow

Add a retry mechanism for scraping failure

Since scraping is a very integrative process it can fail from many causes. Instead of making the user retry himself, it would be good if there was a retry mechanism in the code that would retry x times if the scraping failed.

Run error when following initial setup

After running the initial setup steps - I get a "Cannot find module './categoryCalculationScript'" error.
the "categoryCalculationScript.js" file is in the .gitignore.
is it possible the categoryCalculationScript-example.js file was supposed to be part of the git repository but is not?

Dependabot can't resolve your JavaScript dependency files

Dependabot can't resolve your JavaScript dependency files.

As a result, Dependabot couldn't update your dependencies.

The error Dependabot encountered was:

Error whilst updating dependencies in package-lock.json:
Cannot read property 'match' of undefined

It looks like your lockfile has some corrupt entries with missing versions and needs to be re-generated.
You'll need to remove package-lock.json and node_modules before you run npm install.

If you think the above is an error on Dependabot's side please don't hesitate to get in touch - we'll do whatever we can to fix it.

You can mention @dependabot in the comments below to contact the Dependabot team.

Setup a ci pipline - probably github actions

CI should currently run tests.

In the future can also package the application for installation

Newsletter for new features updates, etc.

Seperate the categories from the logic

To avoid rename the app/node/categoryCalculationScript-example.js when starting to work with the project, and to prepare this project to be installable, we need first to get the categories mapping from an outer file.

In this way, we also can create a global configuration file that will hold many business names, and use it by default, unless the user want to derive or ignore it.

Sheets Exporter: Support new Spreadsheet, new sheet, exists data

After merging #118, need to validate the exporting to Google Spreadsheets code, includes

• Create a new Spreadsheet by name.
• Create a new sheet by const name.
• Read existed data from the sheet and merge with the new data.

Implement an exporter for excel file (csv?) that will be enabled by default with zero configuration needed

All our current exporters require configuration (except for json that will probably not be useful for the common person).
Having a valuable exporter that works out of the box is important because it gives immediate value and will reduce the chance that people will give up during the initial setup to the complexity of it.
So, we can should create a new exporter that exports to an excel file (csv? xlsx?). This exporter will be enabled by default with some default file path.
On the ui of the excel exporter config there will be a link to open the file.

ynab is initialized even if it is not active in the config

#27

Update the readme and add to it the setup flow for the app from scratch

• Update the readme
• Document in Readme the up to date flow of setting up the app from scratch with importers and exporters

Some cards don't use username field

Isracard and amex use id and not username. so you have to manually add those configurations. also, because of the check in
BankScraper.scrape (!credentials.username) user need to add a "dummy" username field as well

id: ,
card6Digits: <6 last digits of card>
password:

Ping

Is this project alive?

I would like to do a similar project.

Make installation of app work on windows

When I try to install by running israeli-bank-scrapers-desktop Setup 0.1.3-beta.exe of unifyRepos, either by building from code or by taking the artifacts from github, it gets stuck on "Installing, please wait".
I was not yet able to find where the log files are.

Show a clear ui status while scraping

The user should see clear feedback about what is currently happening.

Something like the icons of the accounts being scraped and next to each an indication of the status (perhaps with an icon) pending, in progress with loader, done.

Implement an update flow for the app (see auto-update)

Syncing with the main Israeli Bank Scrapers project updates will provide a better experience for the user and eliminate the need to update the software manually.

Handle cases of no exporters, importers or transactions

• Are we should run if no importers defined? (For example, to test the exporters)
• Are we should export if no transactions fetched (empty array)?
• Are we should throw or ignore or event not start to import if no exporters?

The consistent naming convention in the repo is camelCase for directories and files

The consistent naming convention in the repo is camelCase for directories and files so I would rename:

config-manager -> configManager
default-config -> defaultConfig

Originally posted by @brafdlog in #54 (comment)

I think the Vue part is not full compatible with this rule. But also need to think maybe in Vue the convention is different- https://vuejs.org/v2/style-guide/#Single-file-component-filename-casing-strongly-recommended

Financial companies select is displayed incorrectly

#28

Remove irrelevant code of original repositories once the ui and the node code work together

Use repo name and URL from package.config

Instead of using the budget-tracking or https://github.com/brafdlog/budget-tracking in many places in the log, we need to declare them once and use the variable all over the code.

It can be implemented by Webpack DefinePlugin or import { name } from 'package.json.

Add tests for the main node flows

Writing tests for the main flows can be pretty easy if we mock the code that fetches transactions from the financial institutions and mock the code that sends the transactions to ynab/google sheets.
Can also use the csv exporter in tests since it requires no setup

Connect to YNAB through Oauth and not with api key

Current integration with YNAB is through their api key.

This process is annoying to set up and is less natural than the oauth flow that was built for this.

So we should set up the oauth integration with ynab and expose it in the ui of the config screen.

See YNAB docs

Allow passing the user's own encryption key for encryption the financial institution credentials

Currently the encryption key is hard coded. Ideally each user/installation would have his own encryption key.

Improve error handling and event emitting in case of error

Currently every error during the import or export process makes the whole process fail. In addition, there are cases where multiple error events will be emitted for the same error.
We need to decide on a consistent way to handle errors. Probably don't want to crash the whole process because one importer or exporter fail but we do want to indicate the failure clearly to the user

Switch library code to typescript

Break the lib index file into separate files for the major parts of the flow

Original comment:
I want to remove all the code from the root index.ts, to get a clear list about what the backend exporting. I have three (or more) points:

Move all the code from index.ts to separate files in a logical order. The export functionality should be in its file, the same with the import functionality, and the method that takes from import to export in another file.
In this way, we can validate (I hope there is lint for that) the Vue access only to the @/originalBudgetTrackingApp, and the backend itself access only to the flow module, and this module uses its "internal" methods to import and export.
With this structure you can understand half of the code only by its module.

Make the app installable

Make the build process create an installer for mac/windows linux and do the needed adjustments to make it work as an installed app (as opposed to now just being run as a yarn script from the development environment.

Simplify the electron / react / webpack setup

The current setup is an overkill.

We need a simple setup of electron + react + webpack and also typescript (which can be added separately) .

So we can reduce allot of the boilerplate code.

Add to the README a demo video of the scraping and the result in google sheets

Make a short video showing scraping and automatic update of google sheets. Also worth showing the resulting report.

Add this video to the README

Improve the ui of the financial accounts config and make it handle the different financial institution credentials correctly

The current financial account config looks terrible. So one thing is to make it look better - perhaps a card per financial institution?
In addition, this config only supports username and password, but there are many financial institutions that have different credentials (see here).

The config component should support the different variants of credentials so when the financial institution is chosen, allow inserting the credentials that fit that it requires.

Get financial list from 'israeli-bank-scrapers'

The FINANCIAL_COMPANIES_CONFIG in app/web/constants/financialCompaniesConfig.js should be generated from the source list in the israeli-bank-scrapers.

Now, if we want to enhance this list with logo and so on, we need to assign the additional info to the existed list, and handle default values when we did not configure our value yet.

In this way, you don't need to add configuration manually as #25 did.