VBA Grand Master

Apbideas · Jun 13, 2015

Let me know if you need any help automating your spreadsheets, I've been coding in MS Excel Visual Basic for Applications (VBA) for so long now that I can't remember the last time that I ran into something I couldn't automate.

Some solutions I've developed in the past include custom backtesting analytics, custom pattern detection algorithms and real time analysis and recording of time & sales using thinkorswim's COM addin.

Happy hunting.

syswizard · Jun 14, 2015

When did the COM addin become available ?
I remember struggling with their ever-so-awful ThinkScript coding environment about 3 years ago.

xandman · Jun 14, 2015

Do you have programs for IBKR? My interest is in:

Position updates.
Order generation from a field or range.

Contract builder for FOP's - though concatenation would probably be faster.

Apbideas · Jun 14, 2015

It has always been available in some form...before COM it used to be available through DDE. If I remember correctly you access it by clicking the export to Excel option on the watchlist tab.

It's nice having the tape whizzing by in excel because instead of just watching it you can run real time statistics on bid/ask activity, order sizes, option market activity...you can even do some heat map style visualizations that let you watch the tape in a different way.

Apbideas · Jun 14, 2015

I almost...a few times...opened an account at IBKR specifically because I wanted to dig into their Excel tool but I learned a while ago that the type of investing that I am good at doesn't require a lot of automation.

That being said I have total respect and am in awe of people that can successfully use automation to their advantage and would totally enjoy helping anyone do anything...I'm pretty sure that if I played with the IBKR Excel feature for a day I could get it to do pretty much anything including grabbing you a soda and walking your dog.

I played around with FOP symbols in TOS a while ago and was able to do a formula that changes with the contract that is nearest to the money or offset by X number of strikes.

syswizard · Jun 15, 2015

Apbideas said:
I'm pretty sure that if I played with the IBKR Excel feature for a day I could get it to do pretty much anything including grabbing you a soda and walking your dog.

.....until the grim reality sets in:
1) Their Excel DDE workbook is horribly coded and is in need of total rewrite (that would be a great job for you to take on !)
2) Their COM add-in is loaded with quirks and bugs. Subscribe to their API users forum for some real-world feedback.

xandman · Jun 15, 2015

syswizard said:
.....until the grim reality sets in:
1) Their Excel DDE workbook is horribly coded and is in need of total rewrite (that would be a great job for you to take on !)
2) Their COM add-in is loaded with quirks and bugs. Subscribe to their API users forum for some real-world feedback.

Is that why the ActiveX version doesn't have all the functions of TWSDDE? Bugs and likely roadblocks?

syswizard · Jun 15, 2015

I have not tried their Excel COM/ActiveX API workbook. A few years ago they only had the DDE version.
It's not possible for the ActiveX version to not be equivalent functionally to the DDE version because of API limitations.....as the ActiveX API is totally comprehensive.
It's likely the COM/ActiveX version is still a work in progress....and not fully featured.

Apbideas · Jun 15, 2015

It's been my experience that part of the automation game is finding clever work arounds for software bugs and shortfalls. This is what makes a truly successful automation program so hard to come by. Even large instituitional clients suffer from this problem (Knight Capital and Nasdaq OMX).

Some of the ways that I've tried to combat this problem in the past is to keep code as simple as absolutely possible and to also be not totally reliant on technology. With all this focus on automation I think that there is actually growing opportunities of extracting alpha by being low tech and pro human thought.

syswizard · Jun 16, 2015

Apbideas said:
(Knight Capital and Nasdaq OMX).

You forgot the BATS disaster.....http://www.cnbc.com/id/46836415
We should not have to be working around all of these bugs. For the most part a lot of these disasters were due to the hiring of cheap foreign labor for critical programming tasks. That's exactly what happened at Knight and likely at BATS as well. One of the Knight executives was boasting about the money they saved 2 weeks before the disaster. He was fired weeks later.