Menu
Sister Sites

Options

Bookmark Thread

First Page First Unread Last Page Last Post

Print Thread

Similar Threads

MT4 to Excel through Excel RTD (Finally!) 589 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)

  • Post #1
  • Quote
  • First Post: Mar 27, 2013 9:39pm Mar 27, 2013 9: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
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
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.
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
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
Thanks again!!

Massive help
Quoting nomadic123
Thanks again!! Massive help
Happy to help if/when I can
Please Do Not PM Me With Coding Enquiries
What percentage of free margin should you always have available while placing a trade?
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
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?
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
  • Post #12
  • Quote
  • Last Post: Aug 26, 2015 4:27am Aug 26, 2015 4: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