Welcome to NexusFi: the best trading community on the planet, with over 150,000 members Sign Up Now for Free
Genuine reviews from real traders, not fake reviews from stealth vendors
Quality education from leading professional traders
We are a friendly, helpful, and positive community
We do not tolerate rude behavior, trolling, or vendors advertising in posts
We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community. It's free for basic access, or support us by becoming an Elite Member -- see if you qualify for a discount below.
-- Big Mike, Site Administrator
(If you already have an account, login at the top of the page)
I could not find a thread that matched exactly what I am looking for - but it is possible that it is there and I just did not see it. Apologies for any oversight.
I am trying to create an excel sheet that will give me "up to the minute" position sizing for my forex trades. I can just use an online position sizing chart, but I would rather not do that.
I have already created the appropriate tables to get the updated currency rates imported into Excel.
I have no problems with Direct Currency rates of course - those are easy.
My account is in USD.
Let's assume a $25,000 account balance wanting to Risk 1% per trade ($250 / trade).
Trading EURAUD.
We will assume that I want to go long at 1.52417 & a stop at 1.51417. Total of 100 Pip risk.
250/100 = $2.50 per pip.
But since I am trading EURAUD then I need to use the AUDUSD current rate to find out how many AUD / pip.
Current AUDUSD rate: .89615
AUD 2.24 / pip.
That would make a total loss of AUD 224.-- if i get stopped out.
How many Units do I need to buy in order to place this trade? (I realize that the exact rate will change as the prices change - but do we have enough information in order to create a really close estimate?)
Thank you in advance for any help that you for me.
Can anybody help me with the excel formula for this?
I believe you can easily program your formula on excel.
The sequence of operations would be as follows:
1. convert your risk, i.e. 250$ to the base currency of the Forex pair of interest. In your example you want to trade the EUR/AUD, so you need to convert to AUD using the most accurate exchange rate available at that time. So you will get 250USD / 0.89615 = 278.97AUD
2. now calculate the price difference between your entry and your exit. In your example you say buy at 1.52417 and stop at 1.51417 so you have a total distance of 0.01
3. finally derive your position size by dividing your risk in the base currency (in this example AUD) by the price distance. In other words, 278.97AUD / 0.01 = 27,897EUR
So to double check your work: the position size that you should buy on this pair is roughly 28,000 EUR at 1.52417.
If you get stopped at 1.51417 you will lose 280AUD and that's equivalent to 250.922USD at the exchange rate that you state above.
Hope that helps.
Cheers
Fadi
Successful people will do what unsuccessful people won't or can't do!
You wanna short at 102.089 and stop at 102.543 --> so price distance = 0.454
We risk 250USD and that would be equivalent to 250x 102.543 = 25,635.75 JPY.
I am taking the exchange rate at time of being stopped out, as this will be the most accurate
Finally: 25,635.75 / 0.454 = 56,466.41 USD
Let's check for final confirmation:
Sell 56,500 USD.JPY at 102.089 and buy at your stop 102.543 -> so you lose 25,651 JPY
... and that would be equivalent to 25,651 / 102.543 = 250.15 USD
Successful people will do what unsuccessful people won't or can't do!
Here is a small tool i did in Excel 2002 a couple of years ago. If anyone would like to test it just to make sure it also works in the current version of Excel, i would appreciate.
A) Define the various parameters
- Typically, one standard Lot contains 100000 units
- Enter your definition of a mini Lot in fractional form (0.01 ... 1)
- Account size
- Risk Maximum in your portfolio or per trade
- Account currency selected via a pop up menu (USD, EUR, CAD)
There is a button 'Update Values' you should press when you start or open the worksheet or anytime you need to update the various currency rates listed in the worksheet.
The sheet is protected but the password to unprotect is 'trendisyourfriend'. The protection is used to allow only the white fields/cells to be edited.
Set your security level in order to allow the macro to run properly.