ERPNext Accounting setup for stock brokerage company

ERPNext Accounting
,
1

Hi, the question here is how ERPNext accounting module can tackle accounting of stock brokerage company. Need help from accounting experts who can give some pointers on how to setup ERPNext for this type of business, maybe with some modification to the accounts doctype

Use case is as follow.

Let say company ABC is is a stock broker firm.

company ABC registers customers who want to buy and sell securities online from various markets across the globe.

company ABC has an online trading platform through which customers can do the task themselves.

Customer gives company ABC $100 as deposit which would become opening balance of Customer for making online trades.

The opening balance becomes company ABC liability as they need to return this amount when customer demands it.

Customer buys $50 of stock IBM from NYSE from the $100 deposit.

company ABC charges x% commission rate plus tax. In this case 5% charges & 5% tax on commission rate.

company ABC deducts $2.5 commission + 0.125 VAT + $50 for the purchased stock.

$50 has been converted into security so customer balance amount now is $47.375 (100-52.625). company ABC liability is now $47.375.

Customer sell the $50 stock for $60 ($10 profit)

company ABC charges commission + tax on sale. Customer account is debited for 56.850. commission of $3 plus tax 0.150 is deducted from $60 sell transaction.

Customer balance is $104.225 (47.375 + 56.850)

Issue we face is that the security (stock/bond etc.) is not company ABC product. It cannot be recorded as revenue. Only the commission charged on the buy/sell transaction is to be recorded as revenue. However, there needs to be proper tracking of customer balance. Invoice per transaction needs to be made and statement of account on a set frequency needs to be sent to customer outlining all the trades and charges incurred on it.
To ensure authentic information the security name and its relative value has to be recorded but kept off company ABC financial.

Customer accounts needs to be debited and credited which is not possible in ERPNext as it defines Supplier and Customer separately.

Came across this by Grant Thorton. Use case of Futures and options broker on page 7 is similar to company ABC.

Any pointers/help would be highly appreciated.

2

Hi Muzzy,

Interesting deployment.

I realize halfway as I was responding to this, that this reply is more out of curiosity and while intending to be helpful, it may not really be very helpful. Still I am intrigued by your deployment and I responding more out of academic interest, than being insightful. Or even helpful.

Zerodha, the largest Online trading platform in India is on Frappe/ERPNext. The Zerodha team is on the community and is perhaps watching this, so I have to be factual as much as I can. :slight_smile:

I think it will be a force fit to use ERPnext as the online trading platform. Zerodha does a whole bunch of things, they have their own Frappe Framework applications, they also leverage ERPNext for the accounting part, I guess.

I think your best bet is to perhaps use one of the open source trading platforms and enhance the functionality of that platform by integrating your own Frappe Framework applications and ERPNext.

I think Zerodha has it’s own Frappe Framework application for the customer registration process, but once the customer completes registration, a Customer is actually setup on ERPNext so that accounting can happen on ERPNext.

I think this can be treated as an advance payment by the customer. So the app that signs up the customer (could be a Frappe Framework app), credits the customer with $100 on ERPNext.

Your trading platform now integrates with ERPNext and debits the customer for $50.00. Advance from customer is now $50.

Right! However your trading platform integrates with ERPNext and debits the customer for $2.625 and the $2.5 is booked as income on ERPNext and $0.125 as a Tax Liability. Customer Balance is $47.375.

I don’t know the technicalities of how the $50 of IBM stock is treated. Should it be sitting on the books of the trading company? Should it sit as a Customer Balance with the trading company. My guess is that it would still need to figure on the books of the trading company. Some more technical accounting brains maybe able to comment on this.

Here’s where I think it gets technical. Does the value of the holding of the customer keep getting revised with every price feed/change or does it remain static till a transaction (meaning sale) is made? Either ways, now your online trading platform needs to credit the customer account on ERPNext with $60. Customer advance balance is $107.375.

The trading platform makes another accounting entry, debiting the customer account for $3.15, while booking $3 as revenue and $0.15 as a Tax Liability.

Customer balance is $104.125 (or maybe $104.225).

This is perhaps why Zerodha, much as they wanted to :slight_smile: could not use ERPNext for the entire trading platform. I think you have to have another trading platform (it could very well be built on the Frappe Framework, though) and the trading platform at the appropriate times makes the corresponding entries on ERPNext. A Frappe Framework application makes it easier to achieve the integration with ERPNext.

I think your ideal scenario would be a Trading Platform that maintains the Stock Valuations on a Frappe Framework application. The Frappe Framework application in turn makes appropriate accounting entries on ERPNext.

I don’t think this is too much of a problem, as technically I guess you have to treat the $100 from the customer as an Advance. So the undeployed funds of the customer sits as an Advance from customer. You have to check on the deployed funds with a Chartered Accountant or a CPA.

Hope this helps (though I’m guessing it’s not telling you anything you didn’t know already).

Thanks

Jay

1 Like
3

Thanks @JayRam. The trading platform is from a 3rd party. It is in a way white labelled for our client. We have achieved many objectives in past few day through a custom app. Only road block is having negative entry in sales invoice when customer sells the security and gets cash back into the account. And this is a major one.

Is return cannot be used, we tried it. Sometime a customer buys and sells in same day so not just the sell entry but total value can be negative due to more sell value than buy value. We are trying to override it but it doesn’t give consent results.

Let’s see where we reach. One way or other we need to bell the cat.

4

My reply is out of curiosity as well. We had a similar client requirement but turned it down. :+1: You will need to maintain 2 teanants or 2 companies in one tenant. . The broker here is acting behalf of the client (1 broker many clients).

So you can use the stock module to keep track of purchase and sales the broker does. You can also mark to market everyday or show the p&l based on the current rate.

Hope this helps.

5

Thanks @Not_a_countant. We don’t have to track security price. We need to track customer purchase, sales value and deduct market fees, commission and tax. We are now able to get positive and negative values in one invoice as customer may Buy and sell in one day.

Next challenge is to be able to cancel a submitted invoice with negative value as ERPNext does not allow it. It needs positive value.

6

I would still use multi company set up. You can use the buy and sell separately and consolidate into one report and use print format to show them in one report. :+1: Keep us posted.

7

Hi @Not_a_countant

I am a bit confused on your recommendation for multi company. Are you saying each customer should be a company? Why ? How would the Stock broker be able to determine his profitability at the end of the day with this setting ?

Regards

8

Every company has its own P&L. So there is no problem there. He buys and sells like any other company. You will need to customize some reports, but client’s profit is his profit. not the brokers. So multi-company is the way to go.

1 Like
9

Makes sense.

But how will profit flow to the Broker’s “company” from the transactions done on the client’s “company”. At the end of the day this is one if the critical needs.

The inter company Journal entries in ERPNext is not very flexible at the moment. You cannot even have two GLs from two different companies in one Journal entry last time I checked. Or has this changed?

10

There shouldnt be any profit flow, (between broker <-> client) - only deposit / withdrawal. (balance sheet). Not sure if i am explaining it well. :slight_smile: I am not an accountant.

11

Commissions on Trades ?

For every trade made (either sales or purchases) thr broker earns a commission. This Commisison is actually the revenue/turnover for the broker.

This needs to flow from each transaction done on the customers acxount(company)

12

Also buy / sell like intra company. That should do the trick :slight_smile:

13

That is not a viable option for a brokerage firm, especially a successful one with loads of customers. This will require some serious customisation to work.

@Muzzy please keep us posted

Olamide

14

If you get the output, what difference does it make if it is intra company like transaction vs. serious customization? I am curious.

15

The amount of postings you will need to do and the quantum of duplicate postings will make it unusable.

I also use ERPNext internally for my business and I am my own accountant, so I an speak with some confidence in this.

A typical brokerage firm will have hundreds if not thousands of clients and they will generate hundreds of transactions daily/weekly.

Unless of course an API integration between the trading platform and ERPNext will handle this.

16

From my understanding : Zerodha makes 1.3 Billion journal entries a month. That is 42 Million JEs per day. Which is 1.8Million JEs per hour. They have 2 million customers.

Most likely this is a sub broker. And has a few 1000 customers? Just a random guess.
It is definitely not in millions.

17

Lol, you picked the biggest broker in India with 10% of the total client base.

Lots of brokers have just a few thousand clients.

Anyway your response just confirms my point. It is a very interesting post, I do not have a stock brokerage client now or in the pipeline, I am just interested in seeing how @Muzzy solves this challenge. I am simply intellectually curious

1 Like
18

Thanks @Not_a_countant Looks like this maybe the option. The ledger is working vice versa when customer buys and sell security. Example 5000 deposit by customer.
Buys 1000 (assuming all market fees and tax included.) Balance should be 4000 as company liability. System is adding 1000 to customer initial deposit making it 6000.

When customer sells it is deducting whilst it should add to customer balance as money is received from sales.

Overall, a customer and company are buyer and seller to each other as mentioned by you.

1 Like
19

I think that the appropriate solution to this problem is to create a dedicated DocType for recording the sale and purchase of the broker, such as the sale / purchase , and then when submitting this DocType, the sale or purchase invoices will be created depending on it.

You can also create a dedicated DocType for the broker that is linked to the corresponding customer / supplier account to be used when creating sales or purchases invoices.

and a custom report is generated to obtain the broker account statement.

1 Like
20

Happy to say after many trials we got the whole thing working as per client requirement. Too many rules for us to understand and then implement. Frankly, didn’t seem it would be so complex when we started.

Multiple stock exchanges each having different currencies. If buy is more than sell then buy exchange rate else sell. Each exchange has its own set of discounts and trade channels. If trade online then x market fees and y commission rate. If through help desk then x+ and y+ rates. Few exchanges offer stock in multiple currencies and didn’t show the currency in their daily excel report. Pricing in some exchange based on trade value while other based on units traded. Minimum fees per trade or per invoice. One invoice per customer per day per exchange. Options has different rules of commission+market rates. Bonds another rule. Multiple types of print format based on exchanges. Auto changes in invoice wording like Due From You or Due To You based on positive negative value of total invoice. Side by side display of Foreign currencies and base currency. Auto email of sales invoice on submit. Print prefilled legal documents like FATCA, W8Ben, KYC etc. Online registration of customer with auto greetings email with all relevant prefilled customer forms.

And yes we had to make customer as supplier when selling and exchange house as customer. Vice versa for other way around trade. In all we dealt in 9-10 currencies and about 20 stock markets. Now it’s easy for client to add any new exchange with any currency, rates and discount.

Damn not really sure how we achieved it but it’s an amazing feeling. Thank you all for the pointers. Some good points taken from your responses.

Cheers

3 Likes