You must be able to execute the following using exactly the information below. No further information will be given.
I need a simple Excel spreadsheet program that will give me the output for the following:
If price = 1000 and I buy 1 for every point higher the market moves and sell for every 1 point lower the market moves (both longs and shorts at the same time), I will exit when the sum of my buys and sells = 100 points (xpoints). It works like this:
Market moves down 5 points, I sell 1 at every point down. This means I have 5 sells. The market then turns around and begins to move higher. Once it moves back above 1000, I begin to buy 1 at every point above 1000. Once the market moves to 1005, I now have 5 buys and 5 sells. At 1005, I have 5 sells that are losing a total of 40 points and 5 buys making a total of 10 points for a net loss of 30 points. As the market continues to move higher, I add 1 long position. As I add long positions, the net p/l increases faster than the 5 short positions decrease it. By the time I have 22 buys (and still have 5 shorts), my gross profit from all 22 buys is at 231 while my gross loss from all of my 5 sells is at 125. The net profit counting all buys and sells is at 106.
The formula for excel to arrive at this is:
If e2 = 22 (buys)
if f2 = 5 (sells)
the sum of this equation is 106.
Once the 22nd buy level is hit (1022), all positions are exited for a net profit of 106.
However, if the market drops to 990 first and creates 10 sell positions, the market then has to move up to 1031 to total a net profit of 100 because of the 5 extra sells.
What I need is the mathematical calculation for Excel that will figure out where, within any given scenario, a second cycle of trades can be started so that no matter what happens, one of the cycles will close with a profit of (xprofit) before the other cycle "expands" the range.
For example, if there are 5 sells and the market starts moving higher, you need to hit 22 buys to take the profit. However, if you start another cycle at 1016 (16 buys) so that 1016 is another starting place for new buys and sells and the market begins to drop, the second cycle will reach a 100 point profit BEFORE more sells are added to the first cycle at 994. At 995, there would be a total of 21 sells and no more than 5 buys. Once the market hits 994, the range of the first cycle begins to add sell positions while the secondary cycle closes out with a profit.
If another cycle started at 1016 and the market continued to move higher to 1022, the first cycle will have taken profits and the second cycle will have 6 buys started.
I need the mathematical equation that will tell me when I can start that second cycle with the same guarantees no matter what happens, one of the two cycles is going to close.
Another example, if we start cycle 1 at 1000 and have 5 sells and 16 buys, we start cycle 2 as soon as we hit 16 buys. If the market goes to 1021 and we fail to hit 1022 and drop, we close cycle 2 at 994 which is the same level at which the first cycle starts to add sells. When can I start a new cycle with the same guarantee. I cannot start a new one immediately because there are 21 buys in the first one which means I have to have 56 sells (market has to go down to 944) to take a 100 point profit to the downside. If I start a second cycle at 994, the second cycle could to 984 (10 sells) and then start traveling higher. I could take a profit on the second cycle (again) at 31 buys, or a move back up to 1025. But, at 1022, the first cycle starts to expand. If the market moves to 984, the first cycle will have 16 sells and have to move up to 1044. Or can I start a new cycle at 994 immediately and no matter what happens or how the market moves, the second cycle will close with profit without the first one increasing.
The key is that the second cycle closes before the first cycle expands again, or the first cycle closes.
In the second example, if I start a second cycle again at 991 and the market goes to 984, there are 7 sells which means the market only has to go to 26 buys (991 + 26 = 1017) for the second cycle to close out. If the market drops to 976 without moving higher (than 991 which is the starting point), the second cycle would close with 15 sells and no buys. If it went to 977 and then turned around, there would be 14 sells and would need 40 buys (991 + 40 = 1031). But again, the first cycle expands the number of buys at 1022.
26 freelancers are bidding on average $57 for this job
I will do this for you and give you support as well untill you provide me acceptance. I would also like you to escrow the bid amount to my account if you select me Thanks
I am an expert excel user, and can create about any type of spreadsheet, form, workbook that is needed. I have read through the description of the project, and it is manageable.