Bitcoin Forum
March 28, 2024, 04:27:08 PM *
News: Latest Bitcoin Core release: 26.0 [Torrent]
 
   Home   Help Search Login Register More  
Pages: [1]
  Print  
Author Topic: Bitmex Long/Short formulas for excel backtest  (Read 162 times)
Tots (OP)
Newbie
*
Offline Offline

Activity: 4
Merit: 0


View Profile
April 17, 2020, 04:18:30 AM
 #1

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!
The Bitcoin software, network, and concept is called "Bitcoin" with a capitalized "B". Bitcoin currency units are called "bitcoins" with a lowercase "b" -- this is often abbreviated BTC.
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction.
jackg
Copper Member
Legendary
*
Offline Offline

Activity: 2856
Merit: 3071


https://bit.ly/387FXHi lightning theory


View Profile
April 17, 2020, 07:32:53 PM
 #2

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 Offline

Activity: 4
Merit: 0


View Profile
April 18, 2020, 07:41:41 AM
 #3

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 Offline

Activity: 2856
Merit: 3071


https://bit.ly/387FXHi lightning theory


View Profile
April 18, 2020, 11:51:12 AM
 #4

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 Offline

Activity: 4
Merit: 0


View Profile
April 18, 2020, 02:15:54 PM
 #5

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 Offline

Activity: 2856
Merit: 3071


https://bit.ly/387FXHi lightning theory


View Profile
April 18, 2020, 02:52:37 PM
 #6

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).

Tots (OP)
Newbie
*
Offline Offline

Activity: 4
Merit: 0


View Profile
April 19, 2020, 03:35:44 AM
Last edit: April 19, 2020, 03:51:06 AM by Tots
 #7

Ok thanks.

I actually get 2 different results from the 2 PnL calculators I've been referencing, neither of which is $7,500... So it seems I actually now have 3 different results for the same equation...

This one (from: btctools.io/calculators/profit-loss)

https://hosting.photobucket.com/images/p468/DM6000/Screen%20Shot%202020-04-19%20at%201.18.38%20pm.png?width=1920&height=1080&fit=bounds

And this one (downloaded from:   ministryofmargintrading.com/bitmex-calculator)

https://hosting.photobucket.com/images/p468/DM6000/Screen%20Shot%202020-04-19%20at%201.18.29%20pm.png?width=1920&height=1080&fit=bounds


Am I doing something wrong here?

***Edit***

The two online calculators are actually the same, they both give the result of .25 BTC profit (although other figures such as ROE are different).
jackg
Copper Member
Legendary
*
Offline Offline

Activity: 2856
Merit: 3071


https://bit.ly/387FXHi lightning theory


View Profile
April 19, 2020, 06:09:36 PM
 #8

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.
Pages: [1]
  Print  
 
Jump to:  

Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!