Visual Basic

[tommog]

Hi,

Is anyone able to give me a crash course in how you are able to code trading systems in excel using visual basic.

Im not expecting how to be taught how to actually do the coding i will study that myself. I just find it hard to understand how excel, which can work out your balance sheets etc can also be applied to market data and test a system? Where does the data come from and in what form is it stored? Can a code written in visual basic on excel be transferred to a front end trading system?

Im struggling to see how it all fits together. Any snippets of information no matter how basic would be greatly appreciated if it helps me start seeing the bigger picture and how u can use a spreadsheet to test systems.

many thanks

 

[tommog]

really appreciate the response gamma,

i work for a financial institution where i do have programmers i can use, they also are currently building an "environment" in which the company is collating its own tick data. However, i like to try and be as self reliant as possible, you never know what the future holds and the idea of only being able to write systems when i have a teams of quants around me holding my hand through everything makes me feel quite unsettled, so i would like to get my knowledge up to speed so i can programme for myself.

Im currently trading systems in CQG, and although this is quite straight forward to pick up the basics, it is very inflexible in terms of strategies. Also it would be good to be able to have everything in excel, the coding, the execution and the data. Having spoken to a few people on the topic visual basic seems the way forward

 

[dcraig1]

If you are new to programming of any sort, I think you would benefit from taking some course (Excel/VBA). It is much easier these days to teach yourself, given the wealth of online resources, but personal contact is still very beneficial.

In particular you will find that you often become "stuck" on some issue wasting a lot of time searching through documentation, sometimes not even knowing where to look. Access to someone knowledgeable can often solve such issues very quickly. You can ask questions like "How should I go about ....... ?"

A structured course should also provide some guide to "good practice" again bypassing a lot of trial and error.

 

[r.w.]

Hi,

Im not expecting how to be taught how to actually do the coding i will study that myself. I just find it hard to understand how excel, which can work out your balance sheets etc can also be applied to market data and test a system? Where does the data come from and in what form is it stored? Can a code written in visual basic on excel be transferred to a front end trading system?

There's a good book that answers that:

Amazon.com: Modeling Financial Markets : Using Visual Basic.NET and Databases to Create Pricing, Trading, and Risk Management Models: Benjamin Van Vliet, Robert Hendry: Books

 

[tommog]

cheers guys

 

[abottoml]

I get live market data into Excel using Hoadley's Finance Add-In:

Excel Software for Options Traders & Portfolio Investors by Peter Hoadley

The main focus is options, but the add-ins also support the streaming of live quotes on stocks and indices from various providers.

Once you have fresh market data in your spreadsheet, hit Alt-F11 and get jiggy with some VBA code! It's not really at institutional-level speeds or sophistication, but at least you're your own agent.

 

[A Dashing Blade]

Excel Software for Options Traders & Portfolio Investors by Peter Hoadley

Tbh, for AUD106 that looks a bargin.

 

[Zig Zag North]

IMO, the tricky part in VB isn't doing the finance math. The tricky part is importing the quotes from a historical database. I've had success with Yahoo Finance. They allow you to import 20 years of data in .csv files for backtesting. It requires a lot of VB code to make it work smoothly though. Yahoo limits the data so you can't get real time quotes. Yahoo historical quotes are normally delayed which is normally OK for backtesting.

 

[Lucky_81]

If your using CQG they do provide functionality to import live prices into excel via RTD (or maybe DDE not sure if they've upgraded) - give your account mgr a call and they'll explain how to set it up.

 

[MrGecko]

I use Aspen; you can import a fixed length history of prices that keeps updating - e.g. 360 days of daily data = the "rolling" 360 day history, and you can do it with any time period > 1 min, even tick data i think.

Not sure but I think you can do the same thing for indicators, custom or "off the shelf" - i think you can create a custom asset or formula, pull the historical prices into excel, wangle it around, then pump it back into Aspen. I don't really use much of this functionality though.