UnThreaded | Threaded | Whole Thread (11) | Ignore Thread Prev Thread | Next Thread
Author: rharmelink Big red star, 1000 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: of 260  
Subject: Anyone home? EXCEL data extraction? Date: 4/13/2007 2:57 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
Just curious if anyone is still following this board? Or if there is a better forum for discussing SIP?

Either way, I have an add-in that accesses data from the Internet, but wanted a way to also pull in data from the SIP databases. After looking at the problem several times and getting frustrated with it, I'm rather embarassed to say it turns out to be relatively easy. If anyone is interested, let me know. I'll be working on either a separate add-in, or as an additional function (or functions) within my current add-in. I'm just not sure of the best way to allow extraction of data from the databases...
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: 241 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 4/26/2007 4:31 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
I think I was the one who started this particular board, years ago, and I still keep it for sentimental reasons among my "favorites," but obviously don't check all that often.

In any event, yes, indeed, it is fairly easy to pull in data from SIPro databases (or, more exactly, to export from SIPro into Excel) ... and I used to do it all the time, and even developed some really neat ways in Excel to process the data, comparing individual company histories against one another and the industry or sector averages ... etc.

But for various family reasons, I have not had time to keep up with SI Pro, so I've let that subscription lapse (still an AAII member, but relatively passive, just reading the occasional article).

Once you've exported, you really will want to be able to use Excel's database functions, but they're more than adequate, and a lot easier than doing stuff within SIPro (unless they've made massive improvements since I stopped using it)...

There is a forum on the AAII website, and you might find more active interest there. You might ...

Hope that helps,
Mathetes

Print the post Back To Top
Author: rharmelink Big red star, 1000 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 242 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 4/26/2007 4:42 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
Once you've exported...

I used to do that. The stuff I'm working on wouldn't require the data to be exported first. The databases could be opened directly in EXCEL, or a row of data for a given company could be imported directly into EXCEL, or even an individual data field. For example, something like:

=sipGetData("MMM", "EPS_EG5")

To get the estimate of 5-year projected EPS growth for ticker symbol MMM out of the SI Pro databases.

Print the post Back To Top
Author: rharmelink Big red star, 1000 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 243 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 4/26/2007 4:53 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
There is a forum on the AAII website, and you might find more active interest there.

I've not been able to find forums there. Any suggestions?

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: 244 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 4/27/2007 8:22 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
The databases could be opened directly in EXCEL, or a row of data for a given company could be imported directly into EXCEL, or even an individual data field. For example, something like:

=sipGetData("MMM", "EPS_EG5")

To get the estimate of 5-year projected EPS growth for ticker symbol MMM out of the SI Pro databases.


Can you import multiple rows comparing two or more companies? If so, that's good. My whole method of analysis was predicated on being able to look at two or more companies AND an average for the industry group, and that was all much easier to do by exporting a lot of data to Excel in "batch" and then using Excel to make (and graph) comparisons.



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: 245 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 4/27/2007 8:29 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
I've not been able to find forums there. Any suggestions?

Well, I'll have to admit that I hadn't looked at the AAII website for some time. And back when I did post an item or two there, it always seemed as if nobody else was reading them, as if they went into cyberspace and disappeared ... looks like (I just checked again) they disbanded them.


Print the post Back To Top
Author: rharmelink Big red star, 1000 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 246 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 4/27/2007 10:02 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
Can you import multiple rows comparing two or more companies?

That's the plan. Although technically I wouldn't call it an import. The UDF I'm writing would be the equivalent of a table lookup. I already have that with my add-in, but it uses Internet data sources. I'm looking to expand that to use SI Pro as an additional data source.

Right now, I have SQL statements working, which require you knowing physically which database you're getting the data from. I'd prefer to have the function do all of that work for me, so that all I have to do is give it the field name and let it worry about where it comes from. It's complicated by the fact that they index their databases with CUSIP, which means it has to be looked up first.

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: 247 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 4/28/2007 2:12 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 1
You clearly know what you're doing -- I make the assumption, at any rate, that anybody who uses SQL knows what he or she is doing -- my own experience with SQL was limited to mainframe (DB2) work, and it was extensive there, but I never bothered bringing it over to the PC, because I generally found Excel by itself did all I wanted.

If you are interested in seeing what I did in Excel, send me an e-mail (by reply, using that option instead of posting to the boards), and I'll send you a copy of the ages-old Excel sheet I created ... using an export from SIPro of seven years of financial every one of the S&P 500 companies ... and you may see why I opted for that route rather than SQL access to their database. I did the same with the Russell 2000, for small cap companies, a much larger export and resulting in a larger file, but the same notion ...

mathetes

Print the post Back To Top
Author: rharmelink Big red star, 1000 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 248 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 4/28/2007 2:37 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
You clearly know what you're doing...

Maybe, maybe not. Let's just say I have some experience. Doesn't always mean I know what I'm doing. :)

using an export from SIPro of seven years of financial every one of the S&P 500 companies ... and you may see why I opted for that route rather than SQL access to their database

My SQL experience is limited. The collections of data I dealt with professionally were too large for SQL to be of much help.

However, the VFP engine allows a wide variety of access directly from EXCEL, from extraction of a field, row, or even an entire SI Pro database. However, I suspect joining of the databases via SQL in EXCEL would be counterproductive -- that's where SI Pro's screening capabilities would be best used. But I'm looking more for ad hoc extraction and reporting using the SI Pro data.

I already have that capability for data found on the Internet, though my add-in. I just wanted to extend that capability to the SI Pro databases.

Print the post Back To Top
Author: jofj2 9 Year Anniversary! Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 249 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 6/16/2007 2:21 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 0
I am starting again to look for items to assit in using/accessing SIP's databases. A year ago had found "Keelix" on one of the Fool boards, and had started to look at his approach for data extraction to do the real time back testing. But work issues got in the way, so didn't get far.
The following link is still up and should get you to other links to his work:
"_ttp://keelix.com/investing/index.cgi?BacktesterNotes"

hope this helps

Print the post Back To Top
Author: FLARAM Big red star, 1000 posts Old School Fool Add to my Favorite Fools Ignore this person (you won't see their posts anymore) Number: 250 of 260
Subject: Re: Anyone home? EXCEL data extraction? Date: 8/15/2007 9:31 PM
Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Recommendations: 2
I have been using SIPro and Motley Fool for several years and never noticed this board. Maybe I can add to some of the answers to a couple of questions if they haven’t already been answered.

There are several questions about extracting data from SI Pro to EXCEL. You can do this several different ways. The most obvious way is to create a custom view with the fields you want to extract, then use the extract data function which can give you the data you want in several different forms – Excel, CSV, Tab separated and other formats.

Another way is to pull the data directly out of the **.dbf tables, Keelix at "_ttp://keelix.com/investing/index.cgi?BacktesterNotes"; as previously mentioned goes even further and gives you some PHP scripts for extracting the data directly from the SI Pro data and puts into a data base without even installing SI Pro. But that requires a significant software capability beyond the capabilities of most here on the board. I use a commercial software which allows me to extract the data each week and put it in a MySQL database. However at the present time I have to execute rather cumbersome but very powerful SQL Queries to get the data.

I haven’t actually used the program but have talked to a couple of people that love it and was told that the AAII staff use it a program called XLQ that costs $100 if you are an AAII member. This allows direct access from EXCEL. http://www.qmatix.com/XLQ.htm
The example their web site gives is =xlqAAII / xlqAAIIDescrip retrieves over 2800 data points from AAII Stock Investor Pro
e.g. =xlqAAII("msft", "GrossIncomeQ_X",1) will display the quarterly gross income for the latest available quarter.
Additionally you can access Yahoo or IB real time and Yahoo historical quotes from within the same spread sheet.

They also state: All XLQ formulae may also be used via a COM interface from any program or programming language supporting COM. Examples are provided for C++, C#, Visual Basic and Word.

I initially tried writing my own macro’s in EXCEL to retrieve the SI Pro data using Queries, I was able to get many but not all of the tables.

There is a forum on the AAII website, and you might find more active interest there.

The AAII dropped their form during 2003, during the downturn a lot of people dropped their subscription and they laid of staff and stopped supporting the form, I’m sure it didn’t help that some people had critiqued the AAII for their support on the site.

Rharmelink said

Right now, I have SQL statements working, which require you knowing physically which database you're getting the data from.

We may be going down the same path, I have started writing a user interface to select the fields from a menu which will eventually be linked to a SI Pro help menu.

Mathetes said anybody who uses SQL knows what he or she is doing Well I’m using SQL and I’m not sure I know what I’m doing. I just decided I had to jump in, buy a few books and try.

I am defiantly interested in keeping in touch with others working with SI Pro data although as I said my programming skill are limited but improving.

RAM

Post New | Post Reply | Reply Later | Create Poll . Report this Post | Recommend it!
Print the post Back To Top
UnThreaded | Threaded | Whole Thread (11) | Ignore Thread Prev Thread | Next Thread
Advertisement