Hi all; I was hoping someone could help me out on this. I was trying to come up with a macro that would do the following: Imagine I have a column with "b" or "s" and a column with prices. I want for excel to search for the first letter it can, then match it with the FIRST opposite letter, and to take the difference between the two respective prices. Then continue to do this until all the rows are cleared. Ie. 1 row is a "b" at 107 2nd row is "b" at 108, 3rd row "s" at 108, 4th row is "s" at 107. Ideally the macro would match the "b" in row 1 with "s" at row 3 and return 1. Then match "b" in row 2 with row 4 and return -1. Thanks for any help, regards
Just curious - is there any reason why you want a macro to do this? Are you saying this because you assume that you cannot achieve the result with formulae?
Correct. To achieve the ops desired results the formula would need to be recursive. And that's not doable via formulas without manually forcing recalc. And even that would require more data columns for flag purposes. To the OP: I'll post a little somthing for you latter this eve, Dinner bell is ringin.
I've done this with cell formulas but as osorico said, it requires a whole lot columns, one for each possible row entry. I'm looking forward to seeing the macro as well. Thnx
Here you go. The whole xls file with mock data. Not my best work, I had a rather lousy dinner. FWIW; I think this is a limited use concept, as described. All the onus is on the keyboard operator to enter trades in an exact order. Much better to use a trade ID, symbol, datetime or something other than whether buy or sell. jmho Let me know if you need assist modifying it. Osorico
i don't like the way this is thought out.. what if ur shorting too or what if the number of buy and sell signals do not equal - which trade would u fill - or wot trades will the formula use? i think a better idea is to give each trade a unique ascending ID. e.g. from 1 to 10 for example. word example. buy(i) if buy again, then buy(i+1) etc.. to buy(n) if sell then sell (i); P/L = sell(i)-buy(i); new buy(i) = buy(i+1) for all buy orders {re-setting all the buy orders by 1 step) following this ensures a better order integrity.. it also allows handling shorts i.e. if sell (if buy(i)=0)) then resulting P/L = buy(i) - Sell(i) using the same rule as before. example: Buy(1) [price 100] buy(2) [price 110] *buy (1+1)=buy(2) Sell(1)[price 112] = sell(1)-buy(1), PnL = 12, new buy(1) = buy(2) sell(1)[price 120] = sell(1)-buy(1), PnL =10,new Buy(1)=0 *sell again numberd 1 again now continuing selling (shorting) sell(1) [price 120] //when buy(1)=0 ,i.e. shorting sell(2) [price 118] buy(1) [[price 115] = sell(1)-buy(1), PnL=5, new sell(1)=sell(2) [price 118] and so on....
Agree completely with you batman. I eluded to such in my post too... limited use. The macro does EXACTLY what the OP asked for, nothing more. The OPs need is either very narrow, or as you say, not thought out. But hey, maybe we'll get a raise after OP "reviews" our work. Osorico
I also agree with this. One concept missing here is each trade is a tick, but each bid or ask is classified as a bid tick or ask tick. Thus, you may want to consider SIZE and this structure: bid bidsize ask asksize ---- --------- ---- ---------- 40 300 40.5 200 40 200 40.5 200 39.5 500 40 300 So, with the above structure, there is a small amount of redundancy, because each record is unique if there is a change of EITHER bid/bidsize OR ask/asksize. In this example, the bid changed from record 1 to record 2, but the ask remained the same. This structure is much easier to analyze IMHO.
Osorico; Thanks with the code help. All the points Batman/you bring up are valid and I didnt think of them. Therefore, if we could incorporate Batmans loop into the code I think would do the trick. Could you help me modifying your code with batmans idea? Thanks again.