I've been using Excel via IB's DDE connection for a year or so. Excel does my calcs and I use a grid on my 2nd monitor to display the symbol if the stock meets the criteria and a blank cell if not. The problem with this approach is that I have to keep peaking at the 2nd monitor to see if anything new has appeared and this interrupts my focus. To avoid this, I added an audio to beep me if a symbol appears in the grid. This works fine... well, sort of. The problem I'm having is that for a symbol meeting the criteria, for the fraction of a second that it takes for a quote to change, the computer interprets that as a value of zero and with every change of the asked price, the conditional test goes from positive to negative and back to positive and beeps again (if it initially positive). As a simplistic example, suppose I wanted to know when the bid/ask (B/A) difference is greater than 20 cts. That's simple. If (A-B) > .20 then test is true and it beeps once. If the ask goes up by a penny, for that fraction of a second b/t prices displayed, the computer thinks that the asked price is zero and (A-B) becomes negative and the conditional test is false. Now when the new asked price appears, if the condition is met again, it beeps again. IOW, every price change that occurs when the condition is being met results in a beep. When you have 100+ symbols being evaluated, it turns out that the beep symphony is a lot worse than occasionally glancing at the 2nd monitor - particularly in a fast market For the life of me, I can't figure out a set of conditional tests that gets around this problem. I appreciate any suggestions and keep in mind, my math skills are fine but my Excel skills s*ck so if you have an Excel solution, extra bread crumbs please. TIA
Spin, I'm not terribly good at Excel, but since no one else answered I'll give it a try. Why not add an IF statement to ignore any B/A of zero and in such case simply hold the last calculation? BTW, I am pretty sure that Excel would not allow any formulas to execute while any related cells are in the process of changing. So I can't understand how a cell might look like zero due to some Excel transitory condition. So maybe TWS is giving Excel that incorrect "zero' info somehow. Co-incidentally I am developing a similar spreadsheet to beep when a B/A threshold is met. Although I am not sure when I'll actually get to finish it. If I learn anything in the process I'll let you know. Don
Hi Don. How are ya? ... and Happy Holidaze! I'm terrible in Excel. I've been working in Lotus 1-2-3 for 20 years and I still do it there, save it and then open the file in Excel. Too busy so no time to learn Excel. I agree with your conclusion. It's TWS in live mode that is the problem. The brief instance b/t price change is interpreted by Excel as a zero. IOW, for an instant, there's no transmission of price so that's equivalent to zero. As per your other suggestion, I tried a series of IF-THEN conditionals that ignored a B/A of zero but that still gave a flip flopping T/F signal. IOW, it still beeps. Your last suggestion to hold the last calculation is the only solution I have thought of but unfortunately, I don't know how to do that. If you figure out how to do it, I'd appreciate a heads up. Until then, I'm going to use the aforementioned silent "appear in print mode". Spin
If this is similar to what you want to do: If A1 <> zero, then.... Excel may still be seeing something besides a blank cell. Try "" (double quotes) instead of zero in your if/then stmt., or nest them and say <> zero and <> "". E.g.: IF A1<>"", then
I don't see how the first suggestion solves the problem. If I am correct in that the changing of a quote (bid or ask) results in a brief period of no quote which Excel interprets as zero, then A1 <> zero will return a negative conditional test for that brief instant and the moment that the new quote comes through, it reverts back to the positive test result and will beep since the conditional test went from False to True. It's the same problem with just a different way of testing. As for the double quotes suggestion, I think that will be the same problem. However, I will try both of these ideas and see if I get a different result. I'd love to be wrong and admit that you were right (g). Thx for your reply.
You're probably right...I was in a hurry when I replied and didn't completely comprehend what you were saying, so my reply was off the cuff. If I think of something better, I'll let you know. Good luck.
Spin, I am a bit confused about one thing in your question. If the Bid/Ask hits > .20 and then stays > .20, you only want it to beep the first time, or beep every time it legitimately stays over .20? If you only want to hear the beep the first time, I was thinking about something like this: If (A-B)>.20 then beep and also set another cell to 1. Then, if (otherCell <> 1) and (A-b>.20) then beep. So actually, you would check that each time. If (OtherCell <> 1) AND (A-B)>.20 Then Beep and Set OtherCell=1. You could use another column way out for each stock and initialize it to 0, so that the first part would be true at first, but not in future instances. This is kind of common in programming in general - a flag to say something has happened and I don't need to know if it happens again at this point. If you wanted it to keep beeping each time it was actually over .20 difference, I think you would then have the same situation as long as you had bad data. Off hand, I'm not sure of the best way to handle that - maybe there is still someway you could filter the data (is there any way in Excel to not allow certain values in a Cell?, you could make 0 invalid) This is where a small Visual Basic program would be useful - it would be easy to discard any data that was 0 or whatever. Any data you can get into Excel, there should be a fairly easy way to get it into Visual Basic, even though I'm not at all familiar with the IB interface or anything. If you just had bid/ask data constantly coming in though, this really would be easy to do in Visual Basic - if you have any interest, you can download a free version from Microsoft at: www.microsoft.com/express/ Hope I could be of some help, JJacksET4
Spin, I just had another thought. You are apparently trying to toggle based on if (A-B)>.20 or not, but sometimes A is 0, right? Why not compare Ask+Bid then and see if it's in a normal range (i.e. if Ask = 10.00 and Bid = 10.40 then Tot should be 20.40). Something like: If (A-B)>.20) AND (A>= ((A+B)*.30)) then Beep. In other words, A must be at least 30% of the total for the toggle, otherwise it must be invalid and ignore it. You could of course use a different value for the 30%. It seems like that or at least something close to it could work. Or, maybe I am still not quite understanding something about what you are trying to do and the error that is happening. JJacksET4
Spin, If the other suggestions don't work out I tested this and it seems to work: IF(OR(a=0,b=0),CELLx,a-b). CELLx is the cell containing this formula. This formula will hold the last calculation if a or b =0. Else it calculates a-b. One minor problem, If you get a circular reference error goto tools, options, calculation and check the "iterations" box. Happy Holidaze to you also! Don P.S. If you need more info and you want a quicker response send me an email.
Jjacks, Correct. If the Bid/Ask hits > .20 and then stays > .20, I want it to beep the first time. Period. If it subsequently drops below .20 and then goes above .20 again, it should beep again. Once. This is no problem to achieve. The problem is that when the condition is being met (ask-bid > .20), when the ask price changes, it beeps again. and again and again. My assumption is that there is a fraction of a second where there is no quote which the computer interprets as zero thereby resulting in a brief negative conditional test. The moment the new ask price appears, the test becomes positive again. IOW, once the condition is met, every price change in the ask (not bid) results in a beep. I can think of no other reason than the zero price for the ask to make a simple IF A-B > .20 beep with every penny of price change in the ask price. I will test your toggle idea. As for: What happens when the ask goes to zero? Doesn't that create a negative conditional test and when the new ask price is received, it will beep a again? If so, that's still the same problem. As for Visual Basic, that's waaaay above my pay grade thanks for your time and keep those cards and letters coming. Spin Don, Thanks for the suggestion. I'll try it out and get back to ya. So many good ideas, so few technologically functional brain cells (moi)