Calling all Access and Excel experts!
Calling all Access and Excel experts!
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 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:
Regards,
- 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.
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â€
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â€
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 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.
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.
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.
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.
-
- Full Member
- Posts: 22
- Joined: Thu Jul 17, 2003 4:54 am
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
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