Plaid portfolio sync algorithm and calculation improvements #1526

2024-12-10T05:13:22+08:00

zachgoll commented

2024-12-10 05:13:22 +08:00

(Migrated from github.com)

This PR adds the ability to synchronize the Account::Balance and Account::Holding records in both the forward and reverse directions to support both "manual" and "connected" accounts.

Manual account syncs

When dealing with a "manual" account (i.e. an account where the user is managing the data and balances), we must sync the account in the forwards direction.

For a manual account sync, we start the account at $0 and apply all Account::Entry records in chronological order.

test "forward transactions sync" do
  create_transaction(account: @account, date: 4.days.ago.to_date, amount: -500) # income
  create_transaction(account: @account, date: 2.days.ago.to_date, amount: 100) # expense

  expected = [ 0, 500, 500, 400, 400, 400 ]
  calculated = Account::BalanceCalculator.new(@account).calculate.sort_by(&:date).map(&:balance)

  assert_equal expected, calculated
end

Plaid account syncs

When dealing with "connected" account syncs, we must treat the provided balance as the source of truth. In our case, Plaid will provide us with the user's "current balance" (i.e. "current bank balance"). We take that as our source of truth and must work backwards, applying Account::Entry records (also provided by Plaid) in reverse-chronological order to build up the historical balance.

setup do
  @account = families(:empty).accounts.create!(
    name: "Test",
    balance: 20000, # This is the "source of truth" that we work backwards from
    currency: "USD",
    accountable: Depository.new
  )
end

test "reverse transactions sync" do
  create_transaction(account: @account, date: 4.days.ago.to_date, amount: -500) # income
  create_transaction(account: @account, date: 2.days.ago.to_date, amount: 100) # expense

  expected = [ 19600, 20100, 20100, 20000, 20000, 20000 ]
  calculated = Account::BalanceCalculator.new(@account).calculate(reverse: true).sort_by(&:date).map(&:balance)

  assert_equal expected, calculated
end

This also applies to investment holdings. Plaid provides the "current portfolio" (i.e. today's holding quantities, prices, values), which we treat as the source of truth and again, work backwards to construct the historical value of the investment portfolio.

Balance terminology

Depending on the account type, "balance" means different things. In general, the account_balances table represents a daily historical view of the overall "balance" on the account, which translates to:

Investment account - for an investment account, "balance" represents the "total value" when we combine the brokerage cash + holdings market value
Bank account - the "balance" on a bank account simply represents how much cash currently sits in your account
Loan/Credit Card - the "balance" for a liability means, "account owed". A positive balance of $5,000 on a credit card account means, "I owe $5,000 on this credit card right now"

This PR adds a cash_balance field to both Account and Account::Balance tables to more easily display the total "balance" on an account. This field is optional and is primarily used for Investment accounts (cash_balance is "brokerage cash", balance is "total value", and the difference represents the "market value of the holdings")

This PR adds the ability to synchronize the `Account::Balance` and `Account::Holding` records in both the forward and reverse directions to support both "manual" and "connected" accounts. **Manual account syncs** When dealing with a "manual" account (i.e. an account where the user is managing the data and balances), we must sync the account in the _forwards_ direction. For a manual account sync, we start the account at $0 and apply all `Account::Entry` records in chronological order. ```rb test "forward transactions sync" do create_transaction(account: @account, date: 4.days.ago.to_date, amount: -500) # income create_transaction(account: @account, date: 2.days.ago.to_date, amount: 100) # expense expected = [ 0, 500, 500, 400, 400, 400 ] calculated = Account::BalanceCalculator.new(@account).calculate.sort_by(&:date).map(&:balance) assert_equal expected, calculated end ``` **Plaid account syncs** When dealing with "connected" account syncs, **we must treat the _provided_ balance as the source of truth**. In our case, Plaid will provide us with the user's "current balance" (i.e. "current bank balance"). We take that as our source of truth and must work _backwards_, applying `Account::Entry` records (also provided by Plaid) in reverse-chronological order to build up the historical balance. ```rb setup do @account = families(:empty).accounts.create!( name: "Test", balance: 20000, # This is the "source of truth" that we work backwards from currency: "USD", accountable: Depository.new ) end test "reverse transactions sync" do create_transaction(account: @account, date: 4.days.ago.to_date, amount: -500) # income create_transaction(account: @account, date: 2.days.ago.to_date, amount: 100) # expense expected = [ 19600, 20100, 20100, 20000, 20000, 20000 ] calculated = Account::BalanceCalculator.new(@account).calculate(reverse: true).sort_by(&:date).map(&:balance) assert_equal expected, calculated end ``` This also applies to investment holdings. Plaid provides the "current portfolio" (i.e. today's holding quantities, prices, values), which we treat as the source of truth and again, work _backwards_ to construct the historical value of the investment portfolio. **Balance terminology** Depending on the account type, "balance" means different things. In general, the `account_balances` table represents a _daily_ historical view of the overall "balance" on the account, which translates to: - **Investment account** - for an investment account, "balance" represents the "total value" when we combine the `brokerage cash + holdings market value` - **Bank account** - the "balance" on a bank account simply represents how much cash currently sits in your account - **Loan/Credit Card** - the "balance" for a liability means, "account owed". A positive balance of $5,000 on a credit card account means, "I owe $5,000 on this credit card right now" This PR adds a `cash_balance` field to both `Account` and `Account::Balance` tables to more easily display the total "balance" on an account. This field is optional and is primarily used for `Investment` accounts (`cash_balance` is "brokerage cash", `balance` is "total value", and the difference represents the "market value of the holdings")

Sign in to join this conversation.

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: gavin/maybe#1526