Tots (OP)
Newbie
Offline
Activity: 4
Merit: 0
|
|
April 17, 2020, 04:18:30 AM |
|
I'm trying to create an excel sheet for backtesting trading ideas and I'm having trouble figuring out the PnL formulas.
I'm primarily using Bitmex so not sure if formulas are the same across other leveraged trading platforms.
I basically want to have a simple sheet where I can start with a certain amount then go long or short (1x) on each historical price that my chosen indicator signals. Then compound that amount over years to get a simple idea of how a strategy might work if strictly adhered to.
(I'm not including fees for the sake of simplicity at this stage.)
I've tried to reverse engineer the "ministry of margin trading" PnL calculator but can't figure out a few key formulas.
Can someone please tell me what formula I need to add to an excel cell to automatically calculate the profit or loss from a Long or short in Bitmex?
If I'm just buying & selling this is easy: =Exit price - Entry price
But with Bitmex PnL calculators the results seem to be quite complicated.
I'm guessing it needs to be = ( (Exit Price - Entry Price) / Something? ) X Leverage
I'd also be grateful if someone can point me towards an existing (unlocked) excel sheet online that I could download for backtesting.
Also very keen to figure out how to code backtests in Trading View, but this is also taking a bit of work. Any tips or links here also greatly appreciated.
Thanks!
|
|
|
|
jackg
Copper Member
Legendary
Offline
Activity: 2856
Merit: 3071
https://bit.ly/387FXHi lightning theory
|
|
April 17, 2020, 07:32:53 PM |
|
Bitmex has a few options when it comes to the parameters for trading (and I'm not logged in and my second authentication key is far away so I don't know it for sure).
Two options for example though are index and last: If index is ticked, this means the contract follows the index price and pnl is calculated using that (there's a formula but it's mainly the average of the 3 main leaders for btc pairs). If last is ticked, then things execute as the last value reaches this price point and pnl is calculated from the furthest distance from it's direction (or something like that).
Your realised pnl and unrealised pnl are two completely different things too: Realised pnl is what is deducted from your account as a fee for keeping the position open and it's very difficult to calculate as it dynamically changes. Unrealised pnl is what you stand to earn or lose if you close the position now but while the position is open, this just affects your overall balance (hence why margin balance is higher afiak).
The actual formula for unrealised pnl is (entry - exit)*margin position margin position = value * leverage * direction
direction = - (sell/short) / + (buy/long)
|
|
|
|
Tots (OP)
Newbie
Offline
Activity: 4
Merit: 0
|
|
April 18, 2020, 07:41:41 AM |
|
Thanks for the reply!
For the purposes of my backtest I just want a formula that I can apply to historical prices so I guess unrealised PnL would be right as that would equal the trade result if closed at that moment, right?
Does the "value" in that formula represent the position value? i.e. contracts bought?
I also, don't understand the direction formula sorry... do you mean its either minus the sell/short or plus the buy/long... And how do you calculate the short or long in this case?
Apologies for lacking a bit in trading fundamentals, I'm teaching myself all of this stuff with no background in finance/maths but getting there i think...
For example if i went 2x long 75 contracts of BTC with an entry price of $100 and exited that position at $150...
My calculations need to be(?):
Unrealised PnL: (100 [entry] - 150 [exit]) *
Margin Position: 75[value] * 2[leverage] * [direction?]
Direction: - (short?) or + (long?)
Thanks again.
|
|
|
|
jackg
Copper Member
Legendary
Offline
Activity: 2856
Merit: 3071
https://bit.ly/387FXHi lightning theory
|
|
April 18, 2020, 11:51:12 AM |
|
Actually it's exit minus entry in the system:
So a long at 100, sell at 150
150-100 * 2 * 75 (for longing 75 contracts).
If you do a lot of continuous trading, then the realised pnl can go up a bit but you could always neglect it and test out a strategy with a small amount of funds...
Number of contracts bought is known as the "position" or "position margin" Value in my terms was the initial margin so the amount actually on the position of your money (in this case the 75).
|
|
|
|
Tots (OP)
Newbie
Offline
Activity: 4
Merit: 0
|
|
April 18, 2020, 02:15:54 PM |
|
Thanks! I like the simplicity of this, just need to clarify a few things…
How is “Value” different from “position” are they the same thing? In this case 75 contracts bought?
And do you mind explaining direction to me? Is this something that is important for my purposes?
“150-100 * 2 * 75”
The result I get from this is $7,500 which is obviously incorrect: [150-100=50] [50*2=100] [100*75=7,500]
The online PnL calculators give the result as $56.25 (although with 1x leverage it is $75).
How am I interpreting your formula wrong?
Again, thank you so much for your time!
|
|
|
|
jackg
Copper Member
Legendary
Offline
Activity: 2856
Merit: 3071
https://bit.ly/387FXHi lightning theory
|
|
April 18, 2020, 02:52:37 PM |
|
The $7500 looks wrong because you picked quite an extreme example.
2x75=150 dollars in margined position 50x150=7500
BTC amount earnt will be a bit lower due to readjustment for the current price, you're selling the $100 and buying back at a higher cost.
Value and position in my example are the same. Position margin is then position + margin (so the position*leverage).
|
|
|
|
|
jackg
Copper Member
Legendary
Offline
Activity: 2856
Merit: 3071
https://bit.ly/387FXHi lightning theory
|
|
April 19, 2020, 06:09:36 PM |
|
Yeah they're converting the value into btc which youre meant to do, I was just giving a simplified outlook.
You'd take the If you bought a coin at $100 with $150, you'd have 1.5 coins
Selling them at $150 would give you 1.5x150=$225...
So you'd make $150 profit, or 1 coin, so your 1 coin would become 2 (I think but I've been very busy today so this could make no sense) 🤣.
I think this is right and your example is with 75 contracts being bought instead of 150.
|
|
|
|
|