Excel System Development

Quote from JimmyJam:

I use QuoteIn for my future trading model (ES only). You might find them to be useful.

Excel can be a great tool for traders who aren't medium/advanced programmers, because it already has a lot of the infrastructure and logic built into it. It's also very fast to develop a working tool with it.

It all depends on your needs and where you are in your growth as a trader who uses automated tools to assist (or ultimately, assume) their trading.

Good trading,

Jimmy Jam
More...

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">
 
Quote from rosy2:

i have never seen maintainable spreadsheets. how did this consultant do it?
More...
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.
 
Quote from malaka56:

I use excel all day long for trading. Mostly for analysis and data capture via DDE links, but a little for order entry/automation. The DDE links are slow and drive me insane when they stick. The most I run is 1,850 links simultaneously.
More...
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.
 
Quote from JimmyJam:

I use QuoteIn for my future trading model (ES only). You might find them to be useful.

Excel can be a great tool for traders who aren't medium/advanced programmers, because it already has a lot of the infrastructure and logic built into it. It's also very fast to develop a working tool with it.

It all depends on your needs and where you are in your growth as a trader who uses automated tools to assist (or ultimately, assume) their trading.

Good trading,

Jimmy Jam
More...
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.
 
Quote from syswizard:

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.
More...

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.
 
Quote from Rodney King:

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.
More...
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
 
Quote from nitro:

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
More...

You need to round values to the nearest min move after reading them, before you do any processing. This will solve these problems.
 
Quote from nitro:
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#.
nitro
More...
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
 
You must log in or register to reply here.
Back
Top