Automated IB Report Pipeline

Part of my COMP0029 Final Year Project submission at University College London (UCL).

What is this?

This software automates the retrieval and processing of financial data. Specifically, it searches your Gmail for reports generated by InteractiveBrokers' PortfolioAnalyst. These reports are extracted, processed according to your specifications, and uploaded to a Sheet in your Google Drive. This solution is completely stateless, loading reports from Gmail attachments into memory via Report object instances, and uploading them directly to Google Sheets.

Reports are retrieved from email attachments via the Gmail API.
- Processed reports are marked using a label defined in the GmailManager class.
- The report is taken from the most recent email that hasn't been labelled as processed.
The report CSV is broken down, with processing handlers applied to each section to transform the data and run metrics.
The processed CSV is rebuilt and uploaded to Google Drive.
- A single directory, Financial Reports, holds all report spreadsheets in your Google Drive.
- One spreadsheet is made for each year of reports.
- Each month of reports is given its own sheet within its year spreadsheet.

Requirements

Retrieving reports via email

You must have valid Google credentials saved in src/main/resources/credentials.json
You must use your Gmail for your InteractiveBrokers account.
- This is to ensure report emails are delivered to your Gmail.
Reports must be configured in the PortfolioAnalyst Reports section of InteractiveBrokers' website.
Reports, either default or custom, must be configured for email delivery in CSV format.
Reports are assumed to hold data from time period month-to-date at the daily frequency.

Processing report CSVs

You must write your own handlers for processing each report section!
- Example files are already configured under src/main/java/dataprocessing/reportpage/
- Edit the processDataSection method:
  - Its parameter is a CSV in string.
    - The format of the given CSV parameter is the same as that in the email attachment, but of a single section without its section name
    - Strings are delimited by \n for each row, and , for each cell within.
  - It must return a CSV in string format.
By default, if you do not change the processing classes, the report CSV remains unchanged.

Uploading reports

You must have valid Google credentials saved in src/main/resources/credentials.json
Reports overwrite old data in their corresponding sheets, according to the date of the report.

Prerequisites

Gradle

Ensure you have Gradle installed to download dependencies and run the app.

Credentials

You must have Google credentials stored in src/main/resources/credentials.json

You can email michael.khot.19@ucl.ac.uk and request the credential file.

Alternatively, you can set up your own Google Cloud Platform project:

Enable Drive, Gmail, Sheets APIs from the API library.
Set up the OAuth consent screen:
- Enable the following scopes:
  - /auth/gmail.labels - used to create a label to mark already-processed report emails
  - /auth/drive.file - create, access and edit Drive files only used by this app
  - /auth/gmail.modify - used to label emails as processed
- Add your email to the project's test users
Navigate to the credentials tab:
- Select + CREATE CREDENTIALS
- Choose OAuth client ID of type 'Desktop app'
- Download the JSON file
  - Rename it to credentials.json
  - Create a directory and move it to src/main/resources

Running the app

cd to the repository's top level directory.

Enter gradle run into the terminal.

Select your email - which must be among the app's test users - on the OAuth consent screen.

Approve usage of the 3 scopes.

Enjoy your automated IB-reports-to-Google-Sheets pipeline!

michaelkhot/Final-Year-Project