Should i consider using UDF's ? 2

[Mikeb1970]

Hi all,

Most things i program have to do with stockmarket analysis
which i'm sure you all understand has to be very precise since i'm planning in the future to trade by the indicators i calculate. I have made some great programs with my limited knowlegde of VBA, but there is one thing i'm still considering. At this point i can analyze about 400 stocks in 5 minutes. The formulas used are often very lengthy, and often it requires some in between steps to make sure all results are correct, also most formulas have built in error catchers, not allowing the sheet to calculate when there isn't enough data, gaps between data ......

I'm now thinking about writing some UDF's , but the main concern i have is won't this slow my program down???

any thoughts are very welcome,

With regards

Mike

 

[Zathras]

First ask yourself why is it taking 5 minutes for 400 stocks. Does that translate directly into 80 stocks in one minute?

If the time is spent in recalculating the worksheet, macros can indeed speed things up. At the very least, with a macro you can set recalculation to manual, crunch the numbers and then set recalculation back to automatic. Also, simply setting ScreenUpdating off can result in significant time saving.

In general, efficient use of macros would result in faster execution -- not slower.

It's hard to say any more without knowing exactly where the bottle neck is.

 

[rdodge]

In what way are you thinking about adding UDF? I have added various UDFs and not really had a slow down in a majority of the cases. Even in the few cases that I have had slow downs, it was expected due to the recursiveness loopings that was involved, which brings up a good point, try to remove as much recursive coding as much as possible, but if it can't be avoided, try to keep it to a reasonable limit.

Not sure if you have used Excel to some significant level or not, but Excel hasquite a few functions in it that takes very little time, but then there are a few that would take longer due to the recursiveness of the function. One such example would be the SUMIF function. I at one point of time used the SUMIF function in various places, but in that situation, it was recurive in more ways than one. The amount of time for all of the SUMIF functions to calculate was the following:

XF = X number of SUMIF formulae
XR = X number of records
XP = X number of Comparisons
XC = X number of criterias met out of all Comparisons
CT = Comparison Time
ST = Summing Time
TT = Total Time

XP = XF * XR

TT = CT * XP + ST * SC


Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[rdodge]

One other thing about Excel, when it's in automatic mode, it does do a lot of redundant calculations. I had learned how much of a difference it made to change the calculation mode from automatic to manual, use macros to tell what to execute (in this case, what sheet by sheet).

The Example that I have, which is what happened in December of 1998 (Excel 97), I was asked to take over the production reporting system after the plant manager learned how fast I had gotten tasks completed just by using what computer skills that I had at the time (macros wasn't even in my knowledge at the time other than knowing about them). What they had was a basic frame work of how they wanted the information captured, calculated and reported, but didn't even have the links in place and were missing other formulae.

When I took over the reporting system, I spent 2 weeks getting it all setup and ready to go. Right at the start of 1999, I validated the production paper logs, and enter them into the raw data file. Once all data was entered, I then openned the other 18 files. When I did this, it took the program 35 to 45 minutes to open and calculate all of the files. This was an improvement as compared to having to do all of that work over 2 hours of copying and pasting calculated data in different places, but I still felt this was way too long of a time period.

At this point of time, I suspected it was Excel's redundent calculations that was the culprit, and the only way I was going to get out of it without having to manually calculate everything by hand with Shift-F9 was to create a macro for it. Took me about a month on my own to learn the different things and I didn't even know about the different newsgroups or other online help sources. I didn't have anyone else in the company to turn to as I was basically in a league of all to myself as far as the company was concerned. However, after I learned and got a macro recorded, then made a few adjustments, it only took those same 18 files to open and calculate over a time period of 3 to 5 minutes.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.

 

[Mikeb1970]

Hi,

The most time is used to run the queries, each stocks data is in a seperate file. This is done for the following reasons, i prefilter the stocks i wanna look at once a week, save that list as a .csv wich is queried when code starts to run, then send the code into a loop retrieving all data for each stock, this is made dynamic, so when it one time i decide to only look at 200 stocks, the program will only take half the time needed for 400 stocks, since it stops when last stock has been analyzed.

All indicators are calculated on seperate sheets, and then result are linked to one sheet, where in past i also created the charts (lost the charts since i use a professional service now, and will only look at those stocks that passed the scan).

You may have noticed that all this has been build very modular for the simple reason that i want to add, remove things quite fast.

All stocks that pas throw the scanner are added in a report sheet, from wich results are saved , and then queried by another program that adds those stocks for real-time scanning.

Again choose to make this very modular because each puter has his tasks in the network, and when something goes wrong i can easily pinpoint the place where the problem is.

The reason i wanna make these UDF's is very simple , when i decide to remove a column, i always have to check first if those results aren't used in another sheet (linked) avoiding the risk of making a deadlink, this slows me down very much,also would make it allot easier for me to show other investors how to use indicators, (they all are made dynamic, meaning one can play with time frames very simple without the need to rewrite the formulas)

If you are wondering how i am controlling all this, i use 3 puters, one is the master controlling everything, it uses a program called realVNC to take over the other two and then uses automate program to start tasks that should be performed.

At this point no trade whas ever made by the puters, still testing, improving the system.

Any links on the UDF's would be very helpfull

btw i started out with a goal to analyze 1000 stocks in one hour, already achieved my goal and passed it, but i havent seen a thing in my life that couldn't be improved.

 

[rdodge]

Here's a link that goes into the depth of how to work with Functions:

http://www.informit.com/isapi/product_id~{4C079492-5F7E-4570-B9B5-8604555B2A98}/content/index.asp

For the most part, functions are written about the same as procedures in VBA with some minor differences. For the most part, a function is a special type of a procedure that not only executes like a procedure, but also returns a result as set forth within the function. Though I have been working with Access primarily for the last year or so, I have turned some of my procedures into functions so as I can return a value to the previous procedure that called on the subprocedure/function, which by converting the subprocedure to a function, it gave my programming greater flexibility.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
When the going gets tough, the tough gets going.