Traders' Roundtable

Hi all,

I have a query on the use of Access and Excel for system testing. At present I have my system in excel and having 35 years of daily data and umpteen columns of calculations the spreadsheet runs to over 8000 rows and is over 60mb in size. And this is just for one market! This would be fine if I wanted to stop there but as I want to test the system across a portfolio of contracts the spreadsheet will quickly become truly massive.

I know I can export my data to Access but I need to do a variety of calculations (some of which are custom add-ins for excel) to get the required system outputs. Does anyone know if I can use Access to do the grunt work and excel to just summarize the outputs or am I should I just start to learn how to program in C++ now? I'm just having trouble working out how I can get excel to do calculations with data stored in Access without having to import the whole thing and therefore go back to my 60mb spreadsheet. However, looking at Access, it doesn't appear to have the ability to do the if functions etc that excel can do. Am I missing something???

I'm certainly no expert in programming, but I have recently been learning VBA. It would appear you could solve your problems with VBA by either:

• 1. Linking your Access data to an Excel calculation engine, or
  
  2. Allowing you to use one (or many) Excel files as source raw data files and have one (or again, many) separate calculation files or spreadsheets which read, utilise and manipulate that data to perform and report your tests.

No need to have one very large file.

Regards,

Hi,

As a fellow grappler with Excel, albeit several years ago now, your questions brought many memories flooding back.

In my humble opinion, the short answer to your dilemma is stop now! While I applaud your attempts to find answers systematically, I firmly believe you are asking too much of the tools you are currently using. But here’s the good news …. your efforts will be rewarded with a relatively painless change of emphasis.

I too started my system testing work with Excel and each small success fuelled my enthusiasm and generated increased demand for enlightenment. After literally thousands of hours of work I managed to link three generations of spreadsheets together; the first spreadsheet processing price data and generating entry and exit signals, the second applying the required pyramiding rules and adding to the initial positions and the third dynamically sizing the positions with reference to the changing equity balance. All this was controlled using “soft cellsâ€

Thanks for your input guys.

I use Excel for system testing and to some extent I am a dedicated fan, so adjust for my comments accordingly. Many of the comments about Excel’s limitations are valid (eg., “wrong tool for the jobâ€

this thread is a dejavu. sheets of 100mb - yes, yes.

i think many points are valid. in essence i agree with everyone. though they are contradicting

the main advantage of excel cells is the quick programming. if you just use VAB you could as well use some more advanced language. you are basically using the strength of the tool.
the problem with cells is that you have the same information in literally each cell followed in the same row. it is as if you want a computer doing one thousand times adding one number to another and you decide to have one thousand processors, each for one addition.
this is IMHO one reason why the sheets get so big.
the second is that data is often stored within the sheet, which is sometimes unnecessary. better have it in textfiles lying around and import them once needed.

when we used sheets for this purpose we build a tool that would be in the middle between data (left) and output (right). now we build the necessary cpu length in. when the longest period to look back is a hundred days, you need a cpu of at least this length. thus you have block of 100 rows ...
then we let the data on the one side pass by and store the output at the right side. by doing so i reduce my necessary sheet size dramatically.


what i find funny is that excel did not get any better over a decade. no matter how fast our hardware is, the key issue is excel.

peace

I also use a combination of excel and VBA for testing. I find that it is very flexible.

If your program is really slow and you are using if..then statments in excel, try using the logicals in excel. They seem to speed processing time up quite a bit, and are a little easier to follow.

Also, using arrays in VBA speeds things up massivley, but you will need to learn to program.

Also, for file storage and transfer, write two quick macros that will clear all but one of your formulas for storage, and then to expand it when you are using the files.

Excellent ideas guys! I'm busy with CFA study at present, but I'll see if I can squeeze in a bit of VBA training. Mike, how's the CAIA going??

one other thing, if i remember correctly is, that you can let excel calculate only some cells while leaving the others unchanged.

JWebster,

I just got the results last week - passed level I. I might signup for PRMIA exams soon. How's the CFA studies?

Here is an example of a logical in excel

=(d5=23)

If d5 = 23 this will evaluate to true, which also evalauates to 1 (false evaluates to 0), so then multiply it by the condition you want to return when the condition is true.

So =(d5=23)*sum(e5:e20) will give you either the sum or 0, depending on the evalution of the d5=23 part.

You can have and statments by multiplying several logicals together, and or statments by adding them within parenthesis. You can use (d5 <>23) as well.

Should you decide to step outside of Excel/VBA, you may want to check out Python.

The language is simple, elegant, very easy to learn, and yet very powerful. Features-wise it compares favourably with C++ but it's much easier to learn. Frankly, it's probably more than you need to write a test harness.

And if you do try Python, make sure you get the Numeric and scipy modules.

The Numeric module provides a very fast and efficient array data structure that allows you to perform vectorized operations. It is designed to handle hugh datasets (i.e. gigabytes satelite image files) so multiple series of daily price data should be no problem. But then of course a lot depends on your system's logic.

The scipy module builds on top of Numeric and provides various functions in math, stats, etc.

Here are the links:

Python: http://www.python.org

Numeric:
home: http://www.pfdubois.com/numpy/
download: http://sourceforge.net/project/showfile ... ge_id=1351

scipy: http://www.scipy.org

Hi Mike,

CFA doesn't seem overly hard so far, just a lot to cover. As I'm aiming to do level II in June 2005, next 10 months or so are going to be pretty busy. Congratulations on the CAIA pass.