I feel I should warn you now, this is a rather long question I'm doing a rough prototype of something for work (stock market nonsense). What I'm trying to learn is how to build a sort of 'optimization' function in excel. Even though I'm keeping things as basic as possible I've still stumped myself . Here is what I'm working with: 153.76 153.64 153.67 153.71 153.7 153.66 153.69 153.78 153.77 153.79 153.8 153.85 153.86 153.82 153.85 153.92 153.94 153.93 153.97 153.92 153.83 153.91 153.89 153.87 153.88 153.9 153.93 153.88 153.9 153.89 153.91 This is the price of STOCK(X) at the end of every minute, over thirty minutes. Now here is the same thing, with the price of two separate moving averages. One is a faster moving average and one is slower. And here is what it looks like charted: Price is blue, fast is red, slow is green. Now, you can see the fast crosses above the slow. Sometimes it crosses down. Up and down, up and down, all day long depending on where price trevels. Now to the heart of the problem. I've already got a spreadsheet that says "LONG" on the first (and only the first) cell that FAST>SLOW, and it says "SHORT" on the first (and only the first) cell that FAST<SLOW. It's probably becoming clear what I'm working towards now. When you get a FAST>SLOW I want to buy, and when get the opposite I want to flip positions and go short. This picture that might help to explain the general purpose of the FAST and SLOW moving averages as BUY and Sell signals. For right now I don't really care that buying and selling at these prices would've ultimately lost you money, so ignore that for now. Firstly, what I need the spreadsheet to do is calculate profits. So when it sees "LONG" condition it takes the corresponding price and then waits till it sees "SHORT" and calculates the profit for that trade. Then since it is condition "SHORT" it goes short and waits until it sees "LONG" and calculates the profit for that trade. And so on. Of course once you have that, a you can easily get a grand total at the bottom of that column to show you that days performance. Now here is where stuff gets really tricky. And this is going WAY beyond the scope of my knowledge of excel and its abilities, so perhaps it's not possible. I want excel to see the days performance, lets say it's 100$, and then optimize it. I want it to adjust the speed of the two moving averages until days performance = The highest $xxx.xx possible. For reference, a moving average only has one variable. That variable determines the speed/smoothness of the line. (In my examples the FAST moving average has used a variable of 17, and slow of 72.) So really excel just has to replace 17 and 72 with every combination (from 1 to 100 lets say) until it find the most profitable combination. There is roughly 10,000 combinations from 1 to 100. I'm not looking for anyone to do my work for me or anything, rather I'm mainly just needing to know if it can be done. And maybe a small scale example or something. Here is what I've got so far http://aloader.com/files/8/sofar.zip
For the first problem I think this should work: Code: =IF(D2="Long",A2-INDEX(A2:A500,MATCH("Short",E2:E500,0)),IF(E2="Short",A2-INDEX(A2:A500,MATCH("Long",D2:D500,0)),"")) Paste it into F2 and then copy it down. You will get N/A in the last short on your page because it has no long to compare it with after that short. To get around this when you are summing your profits use: Code: =SUM(IF(ISERROR(F2:F1377),"",F2:F1377)) Im not too sure how to accomplish the second part.
This is a big help man. I'm gunna play around with what you suggested tommorow. Big thanks. How'd you get so handy with excel anyways?
When I got my last job, before my recent promotion, the person I was replacing used a ton of excel spreadsheets for moving information, which was half the job. I cleaned them up over the last year and changed it from about 30 hours a week worth of work to about 8. I learned from excel forums and a print out of all the built in functions. Your welcome.
