Hi

As I am shifting from spread betting to 'proper' trading with a broker I had to look at how to do position sizing. It took all evening for me to get my head around the process but I got there eventually. Spread betting is so much easier!! It is just your stake per pip no matter what the currency.
Please read Vahids article at http://www.forexoma.com/money-management-in-forex-trading-how-to-manage-your-money-in-forex/ for the details.

The end result is the attached spreadsheet. I have compared the results with the calculator on the above page and all seems ok.

The instructions are in the sheet but feel free to ask here if you have any questions or want to suggest changes.

All the best

Michael

Hi Michael,

This really looks helpful and straightforward. I had a problem with the position calculator in that the pairs are not listed alphabetically. It took a lot of searching to find what I was looking for. I took the html code of the position calculator and rearranged it into alpha order. I tested it against the source and it works great. I have been using the revised version.

Your Excel spreadsheet with plugins, looks even easier. I will test it out this evening. I'm sure it is accurate, but you know how it goes. It is always good to demo a new idea first.....

Thanks much,

Vincent

Michael,

Taking a quick glance at this, it looks like the plugin will provide realtime quote numbers from Yahoo, so it will be the Yahoo prices that are used as the reference. Is this correct? A difference here is that the position calculator provided by Forexoma/Vahid requires the ask price of the conversion pair when necessary. This ask price I typically get from the ask price of the platform I am placing the trade from, so it is native to the trade.

If one uses the price from yahoo, it may be a few pips off from the prices of the platform, and those few pips could make the difference in a close trade. Am I stating the case as you understand it? If so, has it been your experience that the price difference is negible and irrelevant?.

Thanks,

Vincent

Michael,

I see that you have addressed my questions in your notes. The price information is from Yahoo, and it is delayed data. Disregard.

Thanks,

Vincent

Hi again Michael,

I have had some time during lunch and have crunched some numbers. Overall, the spreadsheet is very clean and easy. There are differences due to the calculation methods between the spreadsheet and the position calculator. The spreadsheet gives the pip value, so an easy stop loss times pips will produce the percentage risk.

The position calculator seems to do more of an average type of calculation. The stop loss times pip does not always yield as close of a percentage risk amount. If I am risking say $100, the spreadsheet will produce a stop loss times pip value of $100. The position calculator will range say $97.00 to $103.00 depending on the pair.

Both fluctuate when I pop the figures into the trading platform. This all has to do, I am sure, with the different methods of calculation and how rounding is done, and pipettes, etc.... Overall, the spreadsheet is nice, clean, and quick.

One note of concern: I believe there may be an error in the calculation of the JPY pairs. I believe it is off by a factor of 100. I have attached a file which will show some random numbers I plugged in. The Units for all xxx\JPY pairs are not correct. Check it out and advise if I may be doing something wrong, or interpreting incorrectly.

Good work!! It will be helpful to many and is an added tool to the already functional position calculator.

By the way, I originally tried the spreadsheet on an old Excel 97, and it did not work. It did run on 2003 and 2007.

Vincent

Hi Vincent

Thanks for the feedback and testing this for me. Based on your screenshot I think I haven't explained this well enough.

The manual column should be left blank if you have installed the plugin. By entering a value in the Manual column you override the Auto value in column P.
Column M (Value) shows the value being used for calcs on that pair.

For the values you entered for CADJPY I get

Stake per pip 2.6295
Units 24514
Lots 0.245

The calc on Forexoma gives the same for Units and Lots.

I use excel 2007 but saved the sheet as 'Excel 97-2003' format.

All the best

Michael

I meant to add that for consistency I entered the value 93.225 in the Manual column so that I had the same numbers as you.

The Auto value as I type is 93.355 which changes the Units to 24548. It doesn't seem that the intra day changes are going to make a huge difference. A 1% change in the value would mean a 1% change in the amount risked on the trade,

If you use 2% risk per trade this means an increase/decrease of 0.02%
1% * 2% = 0.02%

All the best

Michael

I did not enter anything in the manual column. I only entered random stop loss values in the stop loss column starting at C3.

I will check again.

Vincent

Hi Michael,

1. I re-downloaded the file

2. Opened it (by the way, all pairs except the xxx\JPY pairs are set to 2,000 for the unit size, based on GBP / 1,000 / 2%. The xxx\JPY pairs all show a default unit size of 20. Should they also be 2,000 or maybe 200?)

3. I wait for the Yahoo prices to populate

4. I change currency to USD, balance to 5785 (could be anything) and leave the risk at 2%. When I change the currency to USD, all of the default unit values change to 11570, while the xxx\JPY values change to 116.

5. I go to cell C9 (CADJPY), which has a default value of 100, and I change the value to 44. I click cell C10.

6. Units calculate to only 3 digits.

I do not do anything else. It's a really cool tool. I guess it just doesn't like me.

Vincent

6. I tab out of the cell and the units calculate

Hi Michael,

In the version of the spreadsheet available for download, the default formula for calculating the units (other than xxx\JPY) is D(row)*M(row)/0.0001. For all of the JPY pairs, the formula is showing up in my Excel as D(row)*M(row)/0.01. Shouldn't the divisor be 0.0001?

I have attached the spreadsheet untouched from the download. Time is 8:55 PM (20:55)Pacific time. I guess that would be 4:55 AM in Scotland? Why are all of the JPY pairs showing 20 for the unit size, by default?

By the way, since all of the pairs always equal the same number of units for the same stop loss, one could just calculate the first pair and use its unit size even though it may not be the pair being traded.

Thanks,

Vincent

Hi Vincent

The problem is that I had left values of 1 in the Manual column. These need to be deleted and the column left blank to allow the add-in values to be used as shown here.

For anyone not using the add-in, replace the 1s in this column with the latest rates for the pairs.

The divisor for the JPY pairs is different because one pip is the 2nd decimal whereas with the other pairs it is the 4th decimal.

USDGBP 0.6732
USDJPY 93.04

I can't find the explanation I read but once you delete the 1s from the manual column you should see that this returns the same values as Vahids calculator.

Thanks for sticking with this,

Michael

Great.... Now it works..

I know the JPY pairs are two decimals, but not knowing the extent of what your logic was doing, I thought that maybe you were going to get there some other way.

Again, cool tool..

Vincent

This calculator is nice, smooth, and quick Michael. Very efficient. Used it last night on a trade. Just popped in the stop loss, and presto.

Would it be too much work for you to modify it a bit to include some of the other pairs, especially EUR/NZD and NZD/CAD?

Thanks,

Vincent

Thanks Vincent.

I'm happy to make any changes you or others think might be useful so just post your 'wish list' and I'll get on to it soon.

All the best

Michael

Hi Michael,

Should I move this from my "wish list" to my "Christmas list", or is "soon" relative?

Vincent

Hi Vincent

Soon is always relative. Geologists and 5 year old kids both use the word but mean quite different things

I'm busy with other stuff just now but will probably get to updating it this week. Please post a list of the pairs you would like to see included. I have an idea about how to make it completely user definable but will need to see if it works in practice.

All the best

Michael

Hi Michael,

Attached are all the pairs available to me. At your leisure....

Thanks,

Vincent

Hi Vincent

I had a bit of time on my hands today and to be honest the distraction has been welcome. My youngest son has been ill for several days and in hospital since Wednesday. He isn't seriously ill but they have only just diagnosed the problem so the uncertainty has been hard to deal with. Another of those times when we realise the true priorities in life...

I have changed this as I suggested earlier. You now enter your account currency, balance and %risk as before and then you enter the two currencies of the pair separately. The calculator will then get the rate for the correct swap pair and work out the sums. Please have a play and let me know if it works for you.



Thanks

Michael

Thanks Michael,

I will check it out. My best to you and your family. Hope your son gets well soon.

Vincent

Hey there Michael,

I have loaded the spreadsheet. Working as advertised. I cannot adjust the sizing, but the principle is intact. Excellent tool and efficient.

Thanks much,

Vincent