Canopy Financial Statements Module


Canopy
Last Updated: 1 year ago

Disclaimer: Canopy is an Analytics company and does not specialize in fields of accounting and taxation. These calculations are intended to give a customer a sense of how their financial statements would look like. They are offered as a convenience to our customers and are not intended to replace your accounting process or be used for reporting purposes. You should consult your accountant and tax advisor for any accounting, regulatory reporting or taxation matters.

Introduction

Canopy Financial Statements Module (CFSM) can be used to create basic Financial Statements. This module takes all the data inside Canopy Analytics and creates journal entries, ledgers and financial statements. See example output attached here.

Setup

CFSM will create the following tables (if they do not exist already)

  • chart_of_accounts

  • journal_entries

  • ledger

  • balance_sheet

  • profit_loss

Chart of Accounts

These are the backbone of CSFM. CSFM will automatically create a complete set of accounts as follows:

Name

Account Type

Details

Bank Accounts

Current Assets

Canopy will automatically create one account for every currency account in Canopy. e.g. canopy_demo332-ibkr-01-hkd-01. 

We create a separate account for each currency account (even though it ends up being a lot of accounts) because we also track the account balances and reconcile them back with Canopy Analytics as a check to see if all cash entries were processed

Asset Accounts

Current Assets

Canopy will automatically create one account for every currency account in Canopy. e.g. canopy_demo332-ibkr-01-hkd-01. 

We create a separate account for each currency account (even though it ends up being a lot of accounts) because we also track the account balances and reconcile them back with Canopy Analytics as a check to see if all cash entries were processed

Loan

Current Liabilities

One Account for all the Loans taken by the customer

Various

Revenue

Currently these are Distributions, TradingGainLoss, MiscIncome and Interest

Various

Expense

One for each type of expense that needs to be tracked. Currently these are Loan Cost and MiscExpense

ShareCapital

Equity

Represents the shareholders equity in the account. All Inflow, Outflow, Transfer In and Transfer Out transactions hit this account

Journal Entries for Purchase and Sale of Stock in Foreign Currency

Journal entries are created by going through the flash_blotter in chronological order. Let's take the following transactions for NTT (a JPY denominated stock). These are for the canopy_demo account (where the base currency is USD).

Background

The flash_blotter entries are as follows

As we can see this stock was purchased on 31 Dec 2013 and then sold in 2 different lots and also earned a dividend. The market prices and fxrates moved for each transaction.

flash_blotter_id

ticketref

traded_on

settled_on

ticker

quantity

trade_price

trade_type

contract_currency

ccy_account_code

related_security_ticker

user_asset_class

day_1_fxrate_trade_ccy

base_ccy

1805

DEMO1-012568

31-Dec-13

31-Dec-13

9432_JP

3100

5660

Buy

JPY

demo_equity_managed_cssg01_jpy_01

Equity

0.009497

USD

1953

DEMO1-012183

8-May-14

12-May-14

9432_JP

-1600

5648.953

Sell

JPY

demo_equity_managed_cssg01_jpy_01

Equity

0.009837

USD

3144

DEMO1-012187

29-May-14

3-Jun-14

9432_JP

-1500

5989.933

Sell

JPY

demo_equity_managed_cssg01_jpy_01

Equity

0.009824

USD

3152

DEMO1-012287

27-Jun-14

27-Jun-14

Cash

236271

0

Dividend

JPY

demo_equity_managed_cssg01_jpy_01

9432_JP

Cash

0.009862

USD

Purchase of Stock in Foreign Currency

We record the purchase as

(a) Credit the Cash Account with the equivalent amount in base currency i.e. 3100 5660 0.009497 = 166,634.36 .. please remember that all debits and credits are ALWAYS in the accounting currency (i.e. the base currency)

(b) We also record the actual amount in contract currency (i.e. 3100 * 5660 = 17,546,000) for reconciliation purposes in column cash_quantity

(c) Debit Equity (being the investment group dictated by "user_asset_class") for the same amount i,e 166,634.36

(d) We also track the outstanding amount of each purchase in a separate column called security_quantity_outstanding

The journal entries look as follows

entry_date

ticketred

trade_type

ccy_of_entry

entry_ccy_fxrate

base_ccy_fxrate

credit_account_code

credit_amount

debit_account_code

security_ticker

security_quantity

security_quantity_outstanding

cash_quantity

security_trade_price

12/31/2013

DEMO1-012568

Buy

JPY

0.009497

1

demo_equity_managed_cssg01_jpy_01

166,634.36

Equity

166,634.36

3100

3100

-17,546,000

5660

Sale of Stock in Foreign Currency (Lot 1)

Even though the sale price of 5648.953 was lower than our purchase price of 5660, a small gain was made on this sale due to a favourable fx rate movement (from 0.009497 to 0.009837). Even though the total sale consideration was USD 88,910 we need to split it up into a reversal of the original purchase and record the difference as a trading gain. The entries are as follows

(a) Credit Equity with a proportional amount i.e. 166,634.36 x (1,600 / 3,100) = 86,004.83

(b) Debit the Cash Account with the same amount 86,004.83

TradingGainLoss is calculated from the balance amount i.e. 88,910 - 86,004.83 = 2,905.17

Credit(+)/Debit(–) the gain from difference between sale price and average purchase price i.e. Transaction Quantity X (sale price – average purchase price) X (local ccy fx rate / base ccy fx rate) or 1,600 X (5,648.953 – 5,660) X (0.009837 / 1) = –173.87 (Debit)

(c) Debit the PriceTradingGainLoss Account with the amount calculated above i.e. 173.87

(d) Credit the Cash Account for the same amount

(e) Credit the balance amount i.e. 2,905.17 – (–173.87) = 3,079.04 to FxTradingGainLoss

(f) Debit the Cash Account for the same amount

(g) Update the security_quantity_outstanding to 3,100 - 1,600 = 1,500

entrydate

ticketref

trade_type

ccy_of_entry

entry_ccy_fxrate

base_ccy_fxrate

credit_account_code

credit_amount

debit_account_code

debit_amount

security_ticker

security_quantity

security_quantity_outstanding

cash_quantity

security_trade_price

12/31/2013

DEMO1-012568

Buy

JPY

0.009497

1

demo_equity_managed_cssg01_jpy_01

166,634.36

Equity

166,634.36

9432_JP

3100

1500

-17,546,000

5660

5/8/2014

DEMO1-012568

Sell

JPY

0.009837

1

Equity

86,004.83

demo_equity_managed_cssg01_jpy_01

86,004.83

9432_JP

-1600

9,038,325

5648.953

5/8/2014

DEMO1-012568

Sell

JPY

0.009837

1

demo_equity_managed_cssg01_jpy_01

173.87

PriceTradingGainLoss

173.87

9432_JP

0

0

0

5/8/2014

DEMO1-012568

Sell

JPY

0.009837

1

FxTradingGainLoss

3,079.04

demo_equity_managed_cssg01_jpy_01

3,079.04

9432_JP

0

0

0

Sale of Stock in Foreign Currency (Lot 2)

We now sold the remaining quantity of stock at a different price and exchange rate. The accounting entries are similar to the ones above. We also update the security outstanding quantity (which is now zero). Look at the last three rows for the Sell entries:

entrydate

ticketref

trade_type

ccy_of_entry

entry_ccy_fxrate

base_ccy_fxrate

credit_account_code

credit_amount

debit_account_code

debit_amount

security_ticker

security_quantity

security_quantity_outstanding

cash_quantity

security_trade_price

12/31/2013

DEMO1-012568

Buy

JPY

0.009497

1

demo_equity_managed_cssg01_jpy_01

166,634.36

Equity

166,634.36

9432_JP

3100

0

-17,546,000

5660

5/8/2014

DEMO1-012183

Sell

JPY

0.009837

1

Equity

86,004.83

demo_equity_managed_cssg01_jpy_01

86,004.83

9432_JP

-1600

9,038,325

5648.953

5/8/2014

DEMO1-012183

Sell

JPY

0.009837

1

demo_equity_managed_cssg01_jpy_01

173.87

PriceTradingGainLoss

173.87

9432_JP

0

0

0

5/8/2014

DEMO1-012183

Sell

JPY

0.009837

1

FxTradingGainLoss

3,079.04

demo_equity_managed_cssg01_jpy_01

3,079.04

9432_JP

0

0

0

5/29/2014

DEMO1-012187

Sell

JPY

0.009824

1

Equity

80,629.53

demo_equity_managed_cssg01_jpy_01

80,629.53

9432_JP

-1500

8,984,899

5989.933

5/29/2014

DEMO1-012187

Sell

JPY

0.009824

1

PriceTradingGainLoss

4,861.89

demo_equity_managed_cssg01_jpy_01

4,861.89

9432_JP

0

0

0

5/29/2014

DEMO1-012187

Sell

JPY

0.009824

1


FxTradingGainLoss

2,776.23

demo_equity_managed_cssg01_jpy_01

2,776.23

9432_JP

0

0

0

Receipt of Dividend in Foreign Currency

(a) Credit Distribution for the equivalent amount in base currency (i.e. 236,271 x 0.009862 = 2,330.10)

(b) Debit the Cash account for the same amount

entrydate

ticketref

trade_type

ccy_of_entry

entry_ccy_fxrate

base_ccy_fxrate

credit_account_code

credit_amount

debit_account_code

debit_amount

security_ticker

security_quantity

security_quantity_outstanding

cash_quantity

security_trade_price

12/31/2013

DEMO1-012568

Buy

JPY

0.009497

1

demo_equity_managed_cssg01_jpy_01

166,634.36

Equity

166,634.36

9432_JP

3100

0

-17,546,000

5660

5/8/2014

DEMO1-012183

Sell

JPY

0.009837

1

Equity

86,004.83

demo_equity_managed_cssg01_jpy_01

86,004.83

9432_JP

-1600

9,038,325

5648.953

5/8/2014

DEMO1-012183

Sell

JPY

0.009837

1

demo_equity_managed_cssg01_jpy_01

173.87

PriceTradingGainLoss

173.87

9432_JP

0

0

0

5/8/2014

DEMO1-012183

Sell

JPY

0.009837

1

FxTradingGainLoss

3,079.04

demo_equity_managed_cssg01_jpy_01

3,079.04

9432_JP

0

0

0

5/29/2014

DEMO1-012187

Sell

JPY

0.009824

1

Equity

80,629.53

demo_equity_managed_cssg01_jpy_01

80,629.53

9432_JP

-1500

8,984,899

5989.933

5/29/2014

DEMO1-012187

Sell

JPY

0.009824

1

PriceTradingGainLoss

4,861.89

demo_equity_managed_cssg01_jpy_01

4,861.89

9432_JP

0

0

0

5/29/2014

DEMO1-012187

Sell

JPY

0.009824

1


FxTradingGainLoss

2,776.23

demo_equity_managed_cssg01_jpy_01

2,776.23

9432_JP

0

0

0

6/27/2014

DEMO1-012287

Dividend

JPY

0.009862

1

Distribution

2,330.10

demo_equity_managed_cssg01_jpy_01

2,330.10

9432_JP

236,271

Conclusion

At the end of this exercise we have

(a) All credits to Equity (i.e. USD 166,634.36) equal all debit to Equity (USD 86,004.83 + USD 80,629.53 = USD 166,634.36) thereby squaring off the entries.

(b) We have a net FX gain from sale of (–USD 173.87) + USD 2,776.23 = USD 2602.36

(c) We also have trading gains from price changes of USD 3,079.04 + USD 4,861.89 = USD 7,940.93

(d) Finally, we have dividend income of USD 2,330.10

Journal Entries for a Spot Fx Transaction

Let us look at a scenario where the customer bought USD 1mm against JPY on 24 Mar 2018 at a spot rate of 104.72. The market moved in favour of the customer who then took profit on the trade on 19 Jul 2018 at a spot rate of 112.79.

Background

The flash_blotter entries look as follows:

flash_blotter_id

ticketref

traded_on

settled_on

ticker

quantity

trade_price

trade_type

contract_currency

ccy_account_code

related_security_ticker

canopy_asset_class

base_ccy

3515

TRX-000153996

24-Mar-18

24-Mar-18

Cash

1,000,000.00

0

FX Buy

USD

demo_equity_managed_cssg01_usd_01

Cash

USD

3516

TRX-000153997

24-Mar-18

24-Mar-18

Cash

-104,720,000.00

0

FX Sell

JPY

demo_equity_managed_cssg01_jpy_01

Cash

USD

3517

TRX-000153998

19-Jul-18

19-Jul-18

Cash

-928,451.10

0

FX Sell

USD

demo_equity_managed_cssg01_usd_01

Cash

USD

3518

TRX-000153999

19-Jul-18

19-Jul-18

Cash

104,720,000

0

FX Buy

JPY

demo_equity_managed_cssg01_jpy_01

Cash

USD

Purchase of USD against a Foreign Currency (SpotFx Purchase)

We use an account called SpotFxTrade to record all the gains and losses from Fx translation. On 24 Mar 2018 the closing as per Bloomberg was a little higher at 104.7450 (i.e. 0.0250 JPY per 1 USD higher than the transacted rate of 104.72). This will mean a small gain for the customer on their purchase of USD at a rate of 104..72. This gain of 0.0250 JPY per 1 USD is approx USD 238.67 (because 1,000,000 x 0.0250 = 25000 JPY, which is equivalent to 25,000 / 107.7450 = USD 238.67). We should see this gain in the booking. The journal entries are as follows

(a) USD leg: Debit USD cash account and credit SpotFxTrade (both legs for USD 1,000,000)

(b) JPY leg: Credit JPY cash account and credit SpotFXTrade. This leg translates into 104,720,000 / 104.7450 = 999,761.33 for each leg

This means that SpotFxTrade sees a credit of 1,000,000 and a debit of 999,761.84 = gain of USD 238.67 (as expected)

entry_date

ticketred

trade_type

ccy_of_entry

entry_ccy_fxrate

base_ccy_fxrate

credit_account_code

credit_amt_base_ccy

debit_account_code

debit_amt_base_ccy

security_quantity

security_quantity_outstanding

cash_quantity

security_trade_price

24-Mar-18

TRX-000153996

FX Buy

USD

1

1

SpotFXTrade

1,000,000.00

demo_equity_managed_cssg01_usd_01

1,000,000.00

1,000,000.00

24-Mar-18

TRX-000153996

FX Sell

JPY

0.009547

1

demo_equity_managed_cssg01_jpy_01

999,761.84

SpotFXTrade

999,761.84

(104,720,000.00)

Sale of USD against a Foreign Currency (SpotFx Sale)

Now let us look at the take profit transaction where the customer bought back JPY 104,720,000 against USD at a rate of 112.79 (i.e. USD 928,451). The USDJPY SpotFX rate on that day closed at 112.8668. The entries are as follows:

(a) USD leg: Credit USD cash account and debit SpotFxTrade (both legs for USD 928,451.10)

(b) JPY leg: Debit JPY cash account and credit SpotFxTrade (both legs for JPY 104,720,000 divided by closing rate of 112.8668 = USD 927,819.34)

This results in a small translation loss of 927,819.34 - 928,451.10 = - USD 631.76

entrydate

ticketref

trade_type

ccy_of_entry

entry_ccy_fxrate

base_ccy_fxrate

credit_account_code

credit_amount

debit_account_code

debit_amt_base_ccy

security_quantity

security_quantity_outstanding

cash_quantity

security_trade_price

24-Mar-18

TRX-000153996

FX Buy

USD

1

1

SpotFXTrade

1,000,000.00

demo_equity_managed_cssg01_usd_01

1,000,000.00

1,000,000.00

24-Mar-18

TRX-000153997

FX Sell

JPY

0.009547

1

demo_equity_managed_cssg01_jpy_01

999,761.84

SpotFXTrade

999,761.84

(104,720,000.00)

19-Jul-18

TRX-000153999

FX Buy

JPY

0.00886

1

SpotFXTrade

927,819.34

demo_equity_managed_cssg01_jpy_01

927,819.34

112,790,000.00

19-Jul-18

TRX-000153998

FX Sell

USD

1

1

demo_equity_managed_cssg01_usd_01

928,451.10

SpotFXTrade

928,451.10


(1,000,000.00)

Conclusion

(a) Translation Gains: At inception the customer had a translation gain of USD 238.67 and on closing had a translation loss of (USD 631.76) for a total translation loss of (-USD 393.60)

(b) Trading Gains: The customers JPY closing balance is back to zero but the USD cash balance have increased by USD 1,000,000 - USD 928,451.10 = USD 71,548.90. This gain will result in an increase in the networth of the account.

Journal Entries for Derivatives

Introduction

Derivatives can be tricky for the following reason

(a) They are easy to short (and most positions are initiated via a short sale)

(b) Bank Reporting on derivatives is usually sketchy and expiring derivatives often just 'disappear' from the following months closing statements. These are then booked as Transfer Out (or Transfer In if the original position was a short sale) in Canopy and need special treatment.

The following paragraphs summarizes our derivative bookings.

New Positions or Adding to Existing Positions (i.e. same direction as existing trades)

Derivative Trade Type

Further Breakdown

Amount

Credit

Debit

Buy

Purchase Consideration

Cash Account

Security Account

Sell

Sale Consideration

Security Account

Cash Account

Transfer In

Buy Leg of Transfer In

Zero

Cash Account

Security Account

Cash Inflow Leg of Transfer In

Zero

Shareholder Equity in the Company

Cash Account

Transfer Out

Sell Leg of Transfer Out (i.e. short sale)

Zero

Security Account

Cash Account

Cash Outflow leg of Transfer Out

Zero

Cash Account

Shareholder Equity in the Company

Unwind of Existing Positions (full or partial unwind)

Trade Type

Amount

Credit

Debit

Buy to Unwind a Short Sale

Security Account

Cost Basis Amount

Cash Account

Security Account

Gain/Loss if Profit

Gain/Loss Amount

Trading Gain/Loss

Cash Account

Gain/Loss if Loss

abs(Gain/Loss Amount)

Cash Account

Trading Gain/Loss

Sell to unwind a long position

Security Account

Cost Basis Amount

Security Account

Cash Account

Gain/Loss if Profit

Gain/Loss Amount

Trading Gain/Loss

Cash Account

Gain/Loss if Loss

abs(Gain/Loss Amount)

Cash Account

Trading Gain/Loss

Transfer In to unwind a Short Position

This is booked as a Buy to Unwind a Short sale where Purchase consideration Amount is Zero

(i.e Cost basis is equal to acquisition cost of derivative)

Transfer Out to unwind a Long Position

This is booked as a Sell to Unwind a Long Position where Sale consideration Amount is Zero

(i.e Cost basis is equal to acquisition cost of derivative)

T-Account Example for a Call Option. View Excel file here.

Summary of Journal Entries for all Trade Types

Journal Entries depending on the Trade Types are given below:

Trade Type

Credit Account

Debit Account

Treatment

Buy

Cash Account

Asset Account

As shown in above example. Entries passed in equivalent base currency as of Trade Date. A track is kept of outstanding amount and that is adjusted for any Sale

Contribution

Cash Account

Asset Account

Coupon

Distribution

Cash Account

Same for Coupon, Dividend and Distribution

Deposit Drawdown

Cash Account

Asset Account

Accounting similar to a Buy

Deposit Interest

Interest

Cash Account

Deposit Repayment

Asset Account

Cash Account

Similar to a Sale. Reverse out the security_quantity_outstanding for the Investment leg

Distribution

Distribution

Cash Account

Same for Coupon, Dividend and Distribution

Dividend

Distribution

Cash Account

Same for Coupon, Dividend and Distribution

Drawdown

Expense Incurred

Cash Account

MiscExpense

FX Buy

SpotFxTrade

Cash Account

As Above

FX Sell

Cash Account

SpotFxTrade

Income Received

MiscIncome

Cash Account

Inflow

ShareCapital

Cash Account

Loan Drawdown

Liability Account

Cash Account

Loan Interest

Cash Account

LoanCost

Loan Repayment

Cash Account

Liability Account

Outflow

Cash Account

ShareCapital

Reinvestment

Distribution

Cash Account

This is split into two transactions
(a) Dividend Received in Cash

Reinvestment

Cash Account

Asset Account

(b) Purchase of Stock

Sell

Asset Account

Cash Account

This is split into two transactions

(a) Reversal of the original Buy transaction (for the proportional amount of original purchase consideration in base currency)

Sell

Cash Account

GainLoss

(b) GainLoss: For the difference between actual cash received versus the original amount paid

Transfer In

ShareCapital

Cash Account

This is split into two transactions

(a) Inflow

Transfer In

Cash Account

Asset Account

(b) Buy of Security

Transfer Out

Cash Account

ShareCapital

This is split into two transactions

(a) Sale of Security

Transfer Out

Asset Account

Cash Account

(b) Outflow



Was this article helpful?