UnThreaded | Threaded | Whole Thread (6) | Ignore Thread Prev Thread | Next Thread
Author: ProbTrader Two stars, 250 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: of 260  
Subject: Custom fields based on industry medians Date: 12/23/2004 11:10 AM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
SIPro users,

I need to calculate some custom fields based on industry medians. Problems is, the custom field editor window doesn't have a "Medians" tree, like in the View and Screen editors.

I know I can extract industry fields to excel (through the view editor) and then calculate the custome fields myself but I have to do this for all the names in the stock database and I don't think Excel can handle all that data + the SIPro extraction time can be very long.

Is there another workaround?
Print the post Back To Top
Author: mathetes Big red star, 1000 posts Old School Fool Motley Fool One Everlasting Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 221 of 260
Subject: Re: Custom fields based on industry medians Date: 12/29/2004 3:38 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
ProbTrader: I know I can extract industry fields to excel (through the view editor) and then calculate the custome fields myself but I have to do this for all the names in the stock database and I don't think Excel can handle all that data + the SIPro extraction time can be very long.

Why do you have to do it for all the names in the stock database? I do exactly what you're talking about for subsets of the entire database -- e.g., the S&P 500, or for a larger group, the Russell 2000 --- and both SI Pro and Excel can handle that without any trouble.

In general, I've just found it far easier to not even mess around with the custom fields in SI Pro. Excel has so much more power and the formulae are easier to write, and faster to execute.

So it's really not a workaround, but if I were in your shoes I'd question your presumption that you gotta do them all in one extract. Re-think whatever it is you're doing so you only extract, say, one industry group, or a group of related industries, at any single time.

mathetes


Print the post Back To Top
Author: ProbTrader Two stars, 250 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 222 of 260
Subject: Re: Custom fields based on industry medians Date: 12/29/2004 5:44 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
Why do you have to do it for all the names in the stock database? I do exactly what you're talking about for subsets of the entire database -- e.g., the S&P 500, or for a larger group, the Russell 2000 --- and both SI Pro and Excel can handle that without any trouble.

I need to run a filter on the whole database. The only stocks I can think to exclude are OTCs. I'll try to extract the whole database again, but the last time I tried it was well taking 30 minutes on my computer + I'll basically have to replicate what the screen editor does with Excel.


Print the post Back To Top
Author: mathetes Big red star, 1000 posts Old School Fool Motley Fool One Everlasting Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 223 of 260
Subject: Re: Custom fields based on industry medians Date: 1/5/2005 9:17 AM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
ProbTrader: I need to calculate some custom fields based on industry medians. Problems is, the custom field editor window doesn't have a "Medians" tree, like in the View and Screen editors.

I know I can extract industry fields to excel (through the view editor) and then calculate the custome fields myself but I have to do this for all the names in the stock database and I don't think Excel can handle all that data + the SIPro extraction time can be very long.


Let me go back to explain my original question a bit more. I'm challenging your claim that you need to export all or nearly all of the entire SI Pro universe --- which is, as you note, huge ---- but my challenge to that assumption is based on the fact that you are trying to do custom fields based on industry and sector data (medians, etc.)

So, given that what you are trying to calculate is industry/sector specific, why not just export the companies that belong to the industries and sectors in which you are interested. You can surely exclude some whole categories -- industries or sectors --- as being of no value to whatever comparisons and assessments you are ultimately doing.

Now, MY assumption is that you are trying, ultimately, to look for contrasts between individual companies and the industry data. If, on the other hand, what you are doing is aimed ultimately at comparing all of the various industries, per se, then indeed you have a problem. Then I'd begin to question whether SI Pro (in any form, direct or extracted) is the tool for you in this situation.

mathetes

Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Print the post Back To Top
Author: ProbTrader Two stars, 250 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 224 of 260
Subject: Re: Custom fields based on industry medians Date: 1/5/2005 10:47 AM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
Now, MY assumption is that you are trying, ultimately, to look for contrasts between individual companies and the industry data.

You're correct in your assumption, but the problem is that I need to do this for all industries. My screens are not specific to an industry or a sector. In facts the idea behind these screens is to find discrepancies between every stock in the database and its industry.

Last weekend I tried to extract all the fields I need for one of my screens using the last SIPro data download. It took about 20 minutes to produce a 5MB excel file, and this is just for one screen. On the top of that, I will have to run a filter in Excel to highlight the stocks that pass my criterias.

There's a product called XLQ that access all the AAII fields in Excel but it doesn't iterate through the whole ~7000 stocks set. You have to enter one stock at the time, manually. This product wasn't conceived to work as a filter or a screen editor.

If there's a way to iterate through the AAII stock universe and access all the available fields, then I can implement some code in Excel or another language to produce my screens. I know Keelix from the MI board has developed some code to access the AAII database files directly for his backtester but if there's an easier way please let me know.

Many thanks for your replies.

Print the post Back To Top
Author: ajaskey Big gold star, 5000 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 225 of 260
Subject: Re: Custom fields based on industry medians Date: 1/5/2005 11:13 AM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
I use XLQ in Excel with SIP and find it very helpful. I currently dump many fields from SIP to a text file and use Visual Basic and C++ to create filters similar to what you are considering. The XLQ site says it has a COM interface that will work with C++, C#, and VB. I have not used it yet, but it would speed things up if I did. I recommend checking that out as XLQ has a free trial period. If you can get XLQ working as a COM interface, the possibilities of what you can do with the data are bounded by your time and imagination. If I get time and get the COM interface working with C++ or VB I will let you know. I think I tried it about a year ago but couldn't figure it out - mostly because I didn't have the time. As I remember, I think I got it working in VB but could figure out how to add a COM reference to VC++ - mostly ignorance on my part.

Andy

Print the post Back To Top
UnThreaded | Threaded | Whole Thread (6) | Ignore Thread Prev Thread | Next Thread
Advertisement