Creating the General Ledger

Introduction

Most of our customers need to prepare financial statements for the investment activities and for this they typically use some of the popular cloud based accounting systems (e.g. Quickbooks, Xero, Sage etc.) While these systems are very good for normal business activities like invoicing, expenses, payroll, depreciation of fixed assets etc., they usually do not do a very good job of investment accounting.

Of particular problem are journal entries for valuation and sale/purchase of financial assets e.g. things like

  • Realized gains on sale including partial sale of a financial asset
  • Splitting of realized gains into those due to price movement and those due to Foreign Exchange movement,
  • Unrealized gains on balance sheet closing dates
  • Accrued but unpaid interest on balance sheet dates etc.

Usually these entries are calculated manually (or some sort of utility is created). Canopy solves this problem for our customers by generating an appropriate chart of accounts and the general ledger. This can then be uploaded directly into the accounting software (via CSV or API)

Setup

Canopy Financial Statement Module (CFSM) will create the following tables

  • Chart of Accounts
  • General Ledger
  • Balance Sheet (optional)
  • Profit and Loss (optional)

Chart of Accounts

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

NameAccount TypeDetails
Bank AccountsCurrent AssetsCanopy will automatically create one account for every currency account in Canopy. e.g. canopy_demo332-ibkr-01-hkd-01.
Asset AccountsCurrent AssetsCanopy will automatically create one account for every individual security in every currency account in Canopy. e.g. IBM_US held in canopy_demo332-ibkr-01-usd-01 is a separate account.
LoanCurrent LiabilitiesOne Account for all the Loans taken by the customer
VariousRevenueCurrently these are Distributions, TradingGainLoss, MiscIncome and Interest
VariousExpenseOne for each type of expense that needs to be tracked. Currently these are Loan Cost and MiscExpense
ShareCapitalEquityRepresents the shareholders equity in the account. All Inflow, Outflow, Transfer In and Transfer Out transactions hit this account

A sample chart of accounts is here

Sample Journal Entries for Purchase and Sale of Stock in Foreign Currency

Journal entries are created by going through the trade blotter (called Single Line Transactions in 'Canopy Speak') in chronological order from the previous account closing date to the next one. 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).

Transactions Done

The transactions in the trade blotter (i.e. Single Line Transactions) are as follows

blotter_idticketreftraded_onsettled_ontickerquantitytrade_pricetrade_typecontract_currencyccy_account_coderelated_security_tickeruser_asset_classday_1_fxrate_trade_ccybase_ccy
1805DEMO1-01256831-Dec-1331-Dec-139432_JP31005660BuyJPYdemo_equity_managed_cssg01_jpy_01Equity0.009497USD
1953DEMO1-0121838-May-1412-May-149432_JP-16005648.953SellJPYdemo_equity_managed_cssg01_jpy_01Equity0.009837USD
3144DEMO1-01218729-May-143-Jun-149432_JP-15005989.933SellJPYdemo_equity_managed_cssg01_jpy_01Equity0.009824USD
3152DEMO1-01228727-Jun-1427-Jun-14Cash2362710DividendJPYdemo_equity_managed_cssg01_jpy_019432_JPCash0.009862USD

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.

Journal Entries for 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_dateticketredtrade_typeccy_of_entryentry_ccy_fxratebase_ccy_fxratecredit_account_codecredit_amountdebit_account_codedebit_amountsecurity_tickersecurity_quantitysecurity_quantity_outstandingcash_quantitysecurity_trade_price
12/31/2013DEMO1-012568BuyJPY0.0094971demo_equity_managed_cssg01_jpy_01166,634.369432_JP--demo_equity_managed_cssg01_jpy_01166,634.369432_JP31003100-17,546,0005660

Journal Entries for 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

entrydateticketreftrade_typeccy_of_entryentry_ccy_fxratebase_ccy_fxratecredit_account_codecredit_amountdebit_account_codedebit_amountsecurity_tickersecurity_quantitysecurity_quantity_outstandingcash_quantitysecurity_trade_price
5/8/2014DEMO1-012568SellJPY0.00983719432_JP--demo_equity_managed_cssg01_jpy_0186,004.83demo_equity_managed_cssg01_jpy_0186,004.839432_JP-16009,038,3255648.953
5/8/2014DEMO1-012568SellJPY0.0098371demo_equity_managed_cssg01_jpy_01173.87PriceTradingGainLoss173.879432_JP000
5/8/2014DEMO1-012568SellJPY0.0098371FxTradingGainLoss3,079.04demo_equity_managed_cssg01_jpy_013,079.049432_JP000

Journal Entries for 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:

entry_dateticketreftrade_typeccy_of_entryentry_ccy_fxratebase_ccy_fxratecredit_account_codecredit_amountdebit_amount_codedebit_amountsecurity_tickersecurity_quantitysecurity_quantity_outstandingcash_quantitysecurity_trade_price
5/29/2014DEMO1-012187SellJPY0.00982419432_JP--demo_equity_managed_cssg01_jpy_0180,629.53demo_equity_managed_cssg01_jpy_0180,629.539432_JP-15008,984,8995989.933
5/29/2014DEMO1-012187SellJPY0.0098241PriceTradingGainLoss4,861.89demo_equity_managed_cssg01_jpy_014,861.899432_JP000
5/29/2014DEMO1-012187SellJPY0.0098241FxTradingGainLoss2,776.23demo_equity_managed_cssg01_jpy_012,776.239432_JP000

Journal Entries for 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

entry_dateticketredtrade_typeccy_of_entryentry_ccy_fxratebase_ccy_fxratecredit_account_codecredit_amountdebit_account_codedebit_amountsecurity_tickersecurity_quantitysecurity_quantity_outstandingcash_quantitysecurity_trade_price
6/27/2014DEMO1-012287DividendJPY0.0098621Distribution2,330.10demo_equity_managed_cssg01_jpy_012,330.109432_JP236,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 trade blotter entries (i.e. in the Single Line Transactions table):

blotter_idticketreftraded_onsettled_ontickerquantitytrade_pricetrade_typecontract_currencyccy_account_coderelated_security_Tickercanopy_asset_classbase_ccy
3515TRX-00015399624-Mar-1824-Mar-18Cash1,000,000.000FX BuyUSDdemo_equity_managed_cssg01_usd_01CashUSD
3516TRX-00015399724-Mar-1824-Mar-18Cash-104,720,000.000FX SellJPYdemo_equity_managed_cssg01_jpy_01CashUSD
3517TRX-00015399819-Jul-1819-Jul-18Cash-928,451.100FX SellUSDdemo_equity_managed_cssg01_usd_01CashUSD
3518TRX-00015399919-Jul-1819-Jul-18Cash104,720,0000FX BuyJPYdemo_equity_managed_cssg01_jpy_01CashUSD

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_dateticketreftrade_typeccy_of_entryentry_ccy_fxratebase_ccy_fxratecredit_account_codecredit_amt_base_ccydebit_acccount_codedebit_amt_base_ccysecurity_quantitysecurity_quantity_outstandingcash_quantitysecurity_trade_price
24-Mar-18TRX-000153996FX BuyUSD11SpotFXTrade1,000,000.00demo_equity_managed_cssg01_usd_011,000,000.001,000,000.00
24-Mar-18TRX-000153997FX SellJPY0.0095471demo_equity_managed_cssg01_jpy_01999,761.84SpotFXTrade999,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

entry_dateticketreftrade_typeccy_of_entryentry_ccy_fxratebase_ccy_fxratecredit_account_codecredit_amt_base_ccydebit_account_codedebit_amt_base_ccysecurity_quantiysecurity_quantity_outstandingcash_quantitysecurity_trade_price
19-Jul-18TRX-000153999FX BuyJPY0.008861SpotFXTrade927,819.34demo_equity_managed_cssg01_jpy_01927,819.34112,790,000.00
19-Jul-18TRX-000153998FX SellUSD11demo_equity_managed_cssg01_usd_01928,451.10SpotFXTrade928,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 TypeFurther BreakdownAmountCreditDebit
BuyPurchase ConsiderationCash AccountSecurity Account
SellSale ConsiderationSecurity AccountCash Account
Transfer In
Buy Leg of Transfer In
ZeroCash AccountSecurity Account
Cash Inflow Leg of Transfer In
ZeroShareholder Equity in the CompanyCash Account
Transfer Out
Sell Leg of Transfer Out (i.e. short sale)
ZeroSecurity AccountCash Account
Cash Outflow leg of Transfer Out
ZeroCash AccountShareholder Equity in the Company

Unwind of Existing Positions (full or partial unwind)

Trade TypeAmountCreditDebit
Buy to Unwind a Short Sale
Security Account
Cost Basis AmountCash AccountSecurity Account
Gain/Loss if Profit
Gain/Loss AmountTrading Gain/LossCash Account
Gain/Loss if Loss
abs(Gain/Loss Amount)Cash AccountTrading Gain/Loss
Sell to unwind a long position
Security Account
Cost Basis AmountSecurity AccountCash Account
Gain/Loss if Profit
Gain/Loss AmountTrading Gain/LossCash Account
Gain/Loss if Loss
abs(Gain/Loss Amount)Cash AccountTrading Gain/Loss
Transfer In to unwind a Short PositionThis 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 PositionThis 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 TypeCredit AccountDebit AccountTreatment
BuyCash AccountAsset AccountAs 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
ContributionCash AccountAsset Account
CouponDistributionCash AccountSame for Coupon, Dividend and Distribution
Deposit DrawdownCash AccountAsset AccountAccounting similar to a Buy
Deposit InterestInterestCash Account
Deposit RepaymentAsset AccountCash AccountSimilar to a Sale. Reverse out the security_quantity_outstanding for the Investment leg
DistributionDistributionCash AccountSame for Coupon, Dividend and Distribution
DividendDistributionCash AccountSame for Coupon, Dividend and Distribution
Drawdown
Expense IncurredCash AccountMiscExpense
FX BuySpotFxTradeCash AccountAs Above
FX SellCash AccountSpotFxTrade
Income ReceivedMiscIncomeCash Account
InflowShareCapitalCash Account
Loan DrawdownLiability AccountCash Account
Loan InterestCash AccountLoanCost
Loan RepaymentCash AccountLiability Account
OutflowCash AccountShareCapital
ReinvestmentDistribution

Cash Account
Cash Account

Asset Account
This is split into two transactions

(a) Dividend Received in Cash

(b) Purchase of Stock
SellAsset Account

Cash Account
Cash Account

GainLoss
This is split into two transactions

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

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

Cash Account
Cash Account

Asset Account
This is split into two transactions

(a) Inflow

(b) Buy of Security
Transfer OutCash Account

Asset Account
ShareCapital

Cash Account
This is split into two transactions

(a) Sale of Security

(b) Outflow

Did this page help you?