You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
backtesting VB excel code
- Thread starter fortuna
- Start date
Doing the same thing right now, learning VBA in Access. Here's a couple of resources I've been using -
First, get a book from the library, something on the basics of VBA, just to get the broad overview if you don't already have it. This step is not necessary if you aleady have the basics. I've been using two of those big thick books on MS Access 97 (Sams and another publisher) that I bought for a project several years ago, even though they're for an older version they're great for getting the broad lay of the land, and a lot of details have not changed in later versions.
Check out the VBA resources on MSDN.microsoft.com (Microsoft Developer's Network, I think). Click Library, then hunt through the library document tree to find the VBA and VB references. They're hard to find, but look under:
Office Solutions Development >
Microsoft Office >
Microsoft Office 97 >
Product Documentation >
Office >
Under this several Office 97 Visual Basic documents -
VB Programmers Guide and Language Reference
Obviously Office 97 is an outdated version, but there is more VB and VBA information there than for the more recent versions. Also look through the rest of the library for information, and look for DAO (Data Access Objects) and ADO.
Use the VBA info in the Excel help system. In Excel, get to the Visual Basic Editor then go to the help menu. If you don't have the VB help installed, do an install again to add components and add this one.
I've been getting the broad ideas for how to do what I want to do using the books and the MSDN references above, then using the VB help system to figure out the individual details (functions, methods, etc). Don't overlook the link to examples on most help system pages which address specific commands (methods, etc). I just solved a problem that had been bugging me for days, by copying the entire example then making changes to fit exactly what I wanted to do.
Gary
First, get a book from the library, something on the basics of VBA, just to get the broad overview if you don't already have it. This step is not necessary if you aleady have the basics. I've been using two of those big thick books on MS Access 97 (Sams and another publisher) that I bought for a project several years ago, even though they're for an older version they're great for getting the broad lay of the land, and a lot of details have not changed in later versions.
Check out the VBA resources on MSDN.microsoft.com (Microsoft Developer's Network, I think). Click Library, then hunt through the library document tree to find the VBA and VB references. They're hard to find, but look under:
Office Solutions Development >
Microsoft Office >
Microsoft Office 97 >
Product Documentation >
Office >
Under this several Office 97 Visual Basic documents -
VB Programmers Guide and Language Reference
Obviously Office 97 is an outdated version, but there is more VB and VBA information there than for the more recent versions. Also look through the rest of the library for information, and look for DAO (Data Access Objects) and ADO.
Use the VBA info in the Excel help system. In Excel, get to the Visual Basic Editor then go to the help menu. If you don't have the VB help installed, do an install again to add components and add this one.
I've been getting the broad ideas for how to do what I want to do using the books and the MSDN references above, then using the VB help system to figure out the individual details (functions, methods, etc). Don't overlook the link to examples on most help system pages which address specific commands (methods, etc). I just solved a problem that had been bugging me for days, by copying the entire example then making changes to fit exactly what I wanted to do.
Gary
Quote from fortuna:
Hello,
I try to write an excel visual basic code to create a backtesting model on intraday futures signals. Are there some forums where I could gert some free help ?
Thanks
why you did not use something like WL or Amibroker or Omega or whatever with less complicated type of language with already programmed indicators, money management, etc functions?
Good question. I'm sure fortuna will have his own answer, here's mine. I'm thinking that for any particular task there are a number of different tools that could do the job, for the job at hand Access and VBA seem best to me.
I do have and use MetaStock for system testing. For this particular project there were several reasons I decided to do this in Access w/ VBA. 1) I was interested in taking a statistical look at a very specific type of price movement, rather than backtesting a system, and wanted to use some standard statistical methods and output (reports and graphs) that I do not see in MetaStock. 2) The concept I'm looking at includes some outside data other than the standard price/volume data. I did not see an easy wayt collect that data using Metastock, nor an easy way to link to external files with that data (on reflection, the data I'm interested in could probably be put into Metastock's data format in a standard price/volume file and manipulated like a price even though it's not). 3) Other backtesting systems may be able to do what I need done, but I already have MS Office including Access, would rather not put more money into another software package at this point, and would rather not learn another backtesting system language. 4) I wanted to get up to speed on VBA for other purposes, and this seemed to be a good project for that purpose.
I think the general point is a good one - why re-invent the wheel if there's already a tool out there. In this case I had some specific reasons for doing some programming, but I'm wary of the tendency to write code just for the fun of doing it myself. If there's a tool out there that does the job, the goal is to make money, not to write code for entertainment. If there's another tool that does the job without programming, definitely use it.
Gary
I do have and use MetaStock for system testing. For this particular project there were several reasons I decided to do this in Access w/ VBA. 1) I was interested in taking a statistical look at a very specific type of price movement, rather than backtesting a system, and wanted to use some standard statistical methods and output (reports and graphs) that I do not see in MetaStock. 2) The concept I'm looking at includes some outside data other than the standard price/volume data. I did not see an easy wayt collect that data using Metastock, nor an easy way to link to external files with that data (on reflection, the data I'm interested in could probably be put into Metastock's data format in a standard price/volume file and manipulated like a price even though it's not). 3) Other backtesting systems may be able to do what I need done, but I already have MS Office including Access, would rather not put more money into another software package at this point, and would rather not learn another backtesting system language. 4) I wanted to get up to speed on VBA for other purposes, and this seemed to be a good project for that purpose.
I think the general point is a good one - why re-invent the wheel if there's already a tool out there. In this case I had some specific reasons for doing some programming, but I'm wary of the tendency to write code just for the fun of doing it myself. If there's a tool out there that does the job, the goal is to make money, not to write code for entertainment. If there's another tool that does the job without programming, definitely use it.
Gary