Agree JJ'... I use Excel and a RTD feed to build Custom Indicators on the sp500 index so i have the Advance Decline for the 500 instead of using the NYSE A/D, and the Vol Diff for 500, its total net price change and also its true Trin. I have found having these gives a truer read of possible upcoming trend changes or continuations than the somewhat dis information distributed from watching the NYSE Trin, NYSE A/D, NYSE Vol Diff etc... Creating the custom indicators is not hard... Just summation columns... but it took a long time to find a easy and professional way to chart the totals of those custom indicators... Using Sierra Charts as my charting engine I throw (using a VBA timer module) the totals values of my custom indicators into 4 different Sierra intra day chart files every 3 seconds... and Sierra Charts saves and charts the info automatically... works very nicely... <img src="http://www.enflow.com/p.gif">
The keys were: 1) proper variable naming 2) proper code placement 3) creating a function/proc library to avoid redundancy 4) use named ranges whereever possible The last one especially was critical. In fact IB's TWSDDE did NOT have this until the latest release....making any small changes to the layout a nightmare of side-effects and wrong references.
NO wonder.....that's a lot of links. It would be interesting to see if Excel 2007 performs better than 2003 for your app. MSFT finally came out with service pack #2 for 2007, so many of the dumb bugs have been fixed by now.
Quotein looks quite reasonable and I am definitely impressed by Openecry. If I was starting from scratch, I'd try the Quotein/Openecry combo since the programming seems a lot easier. But since I already have all my programming done, I'll stay put for now. But if IB ever becomes unmanageable from a technology, pricing, or customer service stand point, I will definitely look to switch over.
Fair enough. These are reasonable suggestions for working in any programming language. But this is far removed from the typical Excel user's workflow. Anyone who's willing to spend the time, energy and brainpower to enact all these suggestions is at a higher plateau -- and would be able to use any programming environment, not just Excel. The appeal of Excel, what lures people to sail into the rocks, is that's it's quick and easy to get started, without a programming background, and without paying attention to infrastructure rules such as Mr Sys suggests.
In the final analysis, programming in Excel at a low level is not much different than programming in C++ or even Java. As with any dev environment, they are a ton of tricks and traps to be aware of. Yeah, for simply things, Excel is the best alternative....but when they get complex and you hit technical snags, the workarounds are not simple and require a lot of research and testing. Otherwise, less-than-optimal solutions result in an application that runs so slow, it's useless.
I just ported an options model from Excel (VBA) to C#. The code is identical, and yet in VBA I get a value for the call of 23.97 whereas in C# I get a value of 23.39. I have verified that slightly different values for functions like CND between the two languages are being returned, even though the code is identical, once you normalize things like Log in VBA to Math.Log in C#. Those differences in the 4th decimal place made a .57 difference in the value of the calls!!!!!! I then replaced the CND that was written in VBA with one written in native C# and the values concur between Excel and C#. You are forewarned. Don't blindly port stuff, especially stuff that is double precision. nitro
You need to round values to the nearest min move after reading them, before you do any processing. This will solve these problems.
Here is some code I found. Exactly WHERE is the problem with the CND function ? '// The Black and Scholes (1973) Stock option formula Public Function BlackScholes(CallPutFlag As String, S As Double, X _ As Double, T As Double, r As Double, v As Double) As Double Dim d1 As Double, d2 As Double d1 = (Log(S / X) + (r + v ^ 2 / 2) * T) / (v * Sqr(T)) d2 = d1 - v * Sqr(T) If CallPutFlag = "c" Then BlackScholes = S * CND(d1) - X * Exp(-r * T) * CND(d2) ElseIf CallPutFlag = "p" Then BlackScholes = X * Exp(-r * T) * CND(-d2) - S * CND(-d1) End If End Function '// The cumulative normal distribution function Public Function CND(X As Double) As Double Dim L As Double, K As Double Const a1 = 0.31938153: Const a2 = -0.356563782: Const a3 = 1.781477937: Const a4 = -1.821255978: Const a5 = 1.330274429 L = Abs(X) K = 1 / (1 + 0.2316419 * L) CND = 1 - 1 / Sqr(2 * Application.Pi()) * Exp(-L ^ 2 / 2) * (a1 * K + a2 * K ^ 2 + a3 * K ^ 3 + a4 * K ^ 4 + a5 * K ^ 5) If X < 0 Then CND = 1 - CND End If End Function