How to calculate risk in Pips (Excel) | Forex Factory

Options

Permalinks
Print Thread

Similar Threads

MT4 to Excel through Excel RTD (Finally!) 612 replies

how to calculate pips for Yen? 12 replies

I need to calculate ADX in Excel - Please help me 2 replies

Math problem! How do you calculate hedge (hedging) in Excel? 0 replies

How to calculate pips per week? 4 replies

How to calculate risk in Pips (Excel)

  • #1
  • First Post: Mar 27, 2013 8:39pm Mar 27, 2013 8:39pm
Hey guys,

I'm trying to add a column to my trade journal (a Google Spreadsheet) that calculates my risk in pips.

Does anyone know what formula I should use?

The problem is that there are variations on price depending on the currency pair. If everything was "1.xxxx - 1.xxxx" that would be great. But there are pairs which are "123.xx - 123.xx" and "12.xxx - 12.xxx" etc.

So to clarify I want it to come out with a whole number to show how many pips I am risking. Like 30 or 25 or 42 etc.

Here is a screenshot of the spreadsheet to get an idea:

http://img.ctrlv.in/img/51539e144b6a6.png
  • #2
  • Mar 27, 2013 10:16pm Mar 27, 2013 10:16pm
Not sure if this will work out for you or not, I believe that it will return the correct rounded number for most, if not all pairs.
Try it and see.
Paste this into cell I3 and fill down


=ABS(ROUND(IF(F3<10,(G3-F3)*10000,(G3-F3)*100),0))
Please Do Not PM Me With Coding Enquiries
  • #3
  • Mar 28, 2013 11:07pm Mar 28, 2013 11:07pm
THANK YOU! Worked perfectly

While I've got you here - there is one other formula I can't figure out.

I want calculate the time elapsed between between the open and closing of my trades.

For example:

In A1 I have this date/time: 27.03.2013 02:39:29

And B1 I have this date/time: 27.03.2013 10:44:38

I need C1 to calculate the time elapsed between A1 and B1 in hours. Would you happen to know the formula for that?

If I need to split the date/time into different columns or formats thats fine too please let me know.
  • #4
  • Mar 29, 2013 12:23am Mar 29, 2013 12:23am
Quoting nomadic123
THANK YOU! Worked perfectly

While I've got you here - there is one other formula I can't figure out.

I want calculate the time elapsed between between the open and closing of my trades.

For example:

In A1 I have this date/time: 27.03.2013 02:39:29

And B1 I have this date/time: 27.03.2013 10:44:38

I need C1 to calculate the time elapsed between A1 and B1 in hours. Would you happen to know the formula for that?

If I need to split the date/time into different columns or formats thats fine too please let me know.
As far as I know the hh format of cells will not be accurate for periods exceeding 24 hours. Eg 28 hours would show as 4

You could simply enter into C1
=B1-A1
you will need to format the C column by right clicking on the C column and selecting "Format cells" and then "Custom"
If you see dd:hh:mm in the list, then select that. If it's not there, clear the top box and type in dd:hh:mm then click on OK
That would then display a 28 hour result as 01:04:00 ie 1 day and 4 hours
If you never hold a trade for 24 hours or more, then you would only need to format the cell as hh:mm

There are other ways to do it. You could enter into C1
=((B1-A1)*24)
With C1 formatted as a number with 2 decimal places it will show the amount of hours
But, for example 1 hour and 45 minutes would display as 1.75, ie 1 and 3 quarter hours.
If you wanted it to show as hours and minutes it would need 2 columns.

Let me know if you want to know how to do it in 2 columns
Please Do Not PM Me With Coding Enquiries
  • #5
  • Mar 29, 2013 12:32am Mar 29, 2013 12:32am
May as well explain how to do it in 2 columns

in C1 enter
=INT(((B1-A1)*24))

in D1 enter
=(B1-A1)*1440-(C1*60)

Format the C and D columns as numbers with 0 decimal places andd you will have the hours in C1 and the minutes in D1

Hope this helps
Please Do Not PM Me With Coding Enquiries
  • #6
  • Mar 30, 2013 7:58pm Mar 30, 2013 7:58pm
Thanks again!!

Massive help
  • #7
  • Mar 30, 2013 8:04pm Mar 30, 2013 8:04pm
Quoting nomadic123
Thanks again!! Massive help
Happy to help if/when I can
Please Do Not PM Me With Coding Enquiries
  • #8
  • Apr 3, 2013 10:01pm Apr 3, 2013 10:01pm
What percentage of free margin should you always have available while placing a trade?
  • #9
  • Apr 3, 2013 10:31pm Apr 3, 2013 10:31pm
Quoting tony_montana
What percentage of free margin should you always have available while placing a trade?
You don't need a particular %
Your free margin should always be equal or more than the possible loss on all your open trades

ie. if you have $500 free margin, but have trade open that would lose $1000 if it hit SL
once that trade goes into loss by $500, you will have no free margin and you can expect a margin call.
Please Do Not PM Me With Coding Enquiries
  • #10
  • Apr 3, 2013 10:40pm Apr 3, 2013 10:40pm
But on average how much free margin % do you maintain while placing your trades. If you have a strict SL how much are you willing to lose?
  • #11
  • Apr 3, 2013 10:55pm Apr 3, 2013 10:55pm
Quoting tony_montana
But on average how much free margin % do you maintain while placing your trades. If you have a strict SL how much are you willing to lose?
% free margin has no relevance.
How much willing to lose?
That is down to the individual's risk appetite and management.
Please Do Not PM Me With Coding Enquiries
  • #12
  • Last Post: Aug 26, 2015 3:27am Aug 26, 2015 3:27am
Quoting Gumrai
Not sure if this will work out for you or not, I believe that it will return the correct rounded number for most, if not all pairs. Try it and see. Paste this into cell I3 and fill down =ABS(ROUND(IF(F3<10,(G3-F3)*10000,(G3-F3)*100),0))
Hi Gumrai,

Can you help me figure out formula for calculating pips for fx options:

Five columns

Spot price - strike price Premium Intrinsic value(in pips) Extrinsic value (in pips)
(fixed) (variable)
1.1450 - 1.1220 320 (1.1450-1.1220) (230) (Premium value- intrinsic value 320-230) 90

Thank you
0 traders viewing now