Modelling in Excel - Dermot the Actuary...
Pearson Software Consulting
MS Product Team blog
MSDN Language Reference
Martin Greens Office Tips - Independent Software Consultant
Daily Dose of Excel - VBA Code ideas
Contextures - Excel Development Tips
Excel Trader - Excel VBA Trading Platform
Monday, 9 August 2010
Thursday, 22 January 2009
good practice
Here are some things to do which will bear dividends later:
Use 'Option explicit' at the top of every code module. This forces you to declare each variable that you use in your code. This may be a pain at first but it avoids confusion in the code later.
Comment your code. If you start a line of code with an apostrophe that line will be ignored at run time. This means you have the opportunity to write comments on your code as you go. This may not be important at the time but when you come to recycle the code in a years time good commenting will make your life sooo much easier! As a guiding rule if you have as much comment as code that is probably no bad thing.
Keep it short: Break your code down into small modules each with a seperate task. It is better to have ten modules each doing one specific thing rather than one module with 150 lines of code. This means error checking and re-using code will be much simpler later.
Make it recyclable: It will save you a massive amount of time if you can re-use elements of your code many times in different applications rather than re-writing code from scratch. It is worthwhile spending a little more time coding at the outset to make modules easy to use in different applications.
Have a naming convention: Whenever you write something in VBA you will end up creating 'things'. (I'm trying really hard to avoid phrases like 'Object Orientated programming') Have a system for the way that you name 'things'. It will help lots later.
Make names descriptive: By the time you are 18 hours into your development session it will really help if you have rational and logical names for the 'things' that you are working with.
There are many more but that's enough for now.
Use 'Option explicit' at the top of every code module. This forces you to declare each variable that you use in your code. This may be a pain at first but it avoids confusion in the code later.
Comment your code. If you start a line of code with an apostrophe that line will be ignored at run time. This means you have the opportunity to write comments on your code as you go. This may not be important at the time but when you come to recycle the code in a years time good commenting will make your life sooo much easier! As a guiding rule if you have as much comment as code that is probably no bad thing.
Keep it short: Break your code down into small modules each with a seperate task. It is better to have ten modules each doing one specific thing rather than one module with 150 lines of code. This means error checking and re-using code will be much simpler later.
Make it recyclable: It will save you a massive amount of time if you can re-use elements of your code many times in different applications rather than re-writing code from scratch. It is worthwhile spending a little more time coding at the outset to make modules easy to use in different applications.
Have a naming convention: Whenever you write something in VBA you will end up creating 'things'. (I'm trying really hard to avoid phrases like 'Object Orientated programming') Have a system for the way that you name 'things'. It will help lots later.
Make names descriptive: By the time you are 18 hours into your development session it will really help if you have rational and logical names for the 'things' that you are working with.
There are many more but that's enough for now.
data sources
There are lots of sources of data available. Personally I am an avid sharescope user which has lots of data export options. There are many others. For free historic data you can do worse than go to yahoo finance. This gives you the option of downloading historic prices directly into a spreadsheet.
There is a link here to historic FTSE prices. And if your interest is US here is a link to DJIA historic prices. With these links you should then be able to navigate to other securities of interest.
Also worth a mention is the free futures and Forex historical data provide by Trading Blox. You can download ten years of historic data for free here.
Getting accurate data into Excel is probably the biggest drawback of using it to test your trading ideas. There is a great deal to be said for using third party software with dedicated data feeds so that you spend your time testing trading ideas not trying to grab accurate data. But, if you can stand that limitation, excel is still a great place to test your ideas.
If you cannot stand that limitation I believe Trading Blox has a good reputation.
creating a code module
There are lots of sources of information about using excel VBA and I really don't want to repeat it all here. But as a very very quick primer this is how to open the VB editor then create and run a really simply piece of code. This is for Excel 2002. Excel 2007 is slightly different in how you get to the editor but hey, just plug VBA into excel 2007 help and it will tell you.
So, creating your first piece of code: Load excel. Make sure there is a worbook loaded. Save it with a name you will remember. Lets say 'tradetest.xls'
Load the VBE editor. (Either go tools -> Macros -> Visual Basic Editor, or press Alt F11)
You should then see the VBE Editor with a menu structure screen on the left showing current projects and current modules. Right click over the VBA Project(tradetest.xls) , click insert, module.
You should now have a screen into which you can enter your code which should look something like this:
Now lets enter our first piece of code. Type the following into the code window:
________________________
Sub test()
'This a test piece of code
MsgBox "hello trader"
End sub
_______________________
Okay, so this not exactly exciting but you have to start somewhere.
To run the code click on the play button on the VB toolbar.
And you should get a message box in excel like this:
Well done, you have cracked your first piece of code.
So, creating your first piece of code: Load excel. Make sure there is a worbook loaded. Save it with a name you will remember. Lets say 'tradetest.xls'
Load the VBE editor. (Either go tools -> Macros -> Visual Basic Editor, or press Alt F11)
You should then see the VBE Editor with a menu structure screen on the left showing current projects and current modules. Right click over the VBA Project(tradetest.xls) , click insert, module.
You should now have a screen into which you can enter your code which should look something like this:
Now lets enter our first piece of code. Type the following into the code window:
________________________
Sub test()
'This a test piece of code
MsgBox "hello trader"
End sub
_______________________
Okay, so this not exactly exciting but you have to start somewhere.
To run the code click on the play button on the VB toolbar.
And you should get a message box in excel like this:
Well done, you have cracked your first piece of code.
Why use VBA?
You probably have a good market information system. But if you are a trader or investor you will eventually wish to test your own trading and investment ideas. There are great pieces of software out there that let you do just that. But if you have a copy of MS Excel then you already have the basis of a system testing tool. If you want to test your own trading ideas quickly then this blog is probably for you.
Of course you can also use Excel for real time data. Above is a screen shot of the FTSE 100 constituents in real time. It shows all of the FTSE 100 constituents and, at a glance, lets you see where they sit within their normal trading ranges.
This system flags up stocks that cross support and resistance levels with an audible warning, reading out the ticker, price, and the event that has triggered the alarm. This is handy if, like many retail traders, you work from home alone, and don't necessarily want to spend your whole day glued to the screen.
We need to do something fairly useful quite quickly. In my next post I'll set out how to open the VBE editor, create a code module and write then run a simple piece of code. After that we will grab data from your spread sheet, pull it into memory, do something with it, and put it back on the spreadsheet.
Monday, 19 January 2009
Excel VBA - books and links
Four web sites:
Three Books:
Excel VBA Programming for Dummies
John Walkenbach
This is full of quick simple and easy to use VBA tips. You can pick this up and benefit from it without any prior knowledge. If you are going to follow this thread and don't have any prior experience programming it would be really useful to have a copy handy.
Professional Excel Development:
The Definitive Guide to Developing Applications Using Microsoft Excel and VBA Bullen, Bovey, Green.
I love this book. Full of best practices, programming tips and working examples.
And you can read it. If you are happy with the basic elements of VBA this should be useful. I can't say that I have read the book cover to cover (I do have a life) but I use a lot of the code in my own development. And it is worthwhile reading the best practice hints and tips before starting any serious development.
Excel 2007 VBA Programmer's Reference
John Green, Stephen Bullen, Rob Bovey, Michael Alexander.
This much more of a reference book and, while I have used snippets of code, I have not read the book from cover to cover. (Come on, it's a reference book - let's use it for reference).
Three Books:
Excel VBA Programming for Dummies
John Walkenbach
This is full of quick simple and easy to use VBA tips. You can pick this up and benefit from it without any prior knowledge. If you are going to follow this thread and don't have any prior experience programming it would be really useful to have a copy handy.
Professional Excel Development:
The Definitive Guide to Developing Applications Using Microsoft Excel and VBA Bullen, Bovey, Green.
I love this book. Full of best practices, programming tips and working examples.
And you can read it. If you are happy with the basic elements of VBA this should be useful. I can't say that I have read the book cover to cover (I do have a life) but I use a lot of the code in my own development. And it is worthwhile reading the best practice hints and tips before starting any serious development.
Excel 2007 VBA Programmer's Reference
John Green, Stephen Bullen, Rob Bovey, Michael Alexander.
This much more of a reference book and, while I have used snippets of code, I have not read the book from cover to cover. (Come on, it's a reference book - let's use it for reference).
Sunday, 18 January 2009
Introduction
This blog is about trading, trading systems, trading system design, trading with excel, trading despite excel, enjoying excel for trading development, and hating excel for trading development.
(It's a love hate relationship.)
The story in short is:
(It's a love hate relationship.)
The story in short is:
- Have (or read) trading idea.
- Test trading idea.
- Test more complex trading idea.
- Develop 50mb work book that falls over a lot.
- Discover VBA - and arrays.
- Start developing effective trading ideas.
Subscribe to:
Posts (Atom)
Posts
