Batch stock info lookup in Excel 2003

[JonB]

I've been using Excel for years now to help me track information on stocks. However, I have 30 stocks or so that I normally look-up on the Internet, then enter the data into Excel. It takes me around 1.5 hours each week to update all these numbers. I figure there HAS to be an easier way to do this.

My sheet looks something like this:

Stock____1yr Rank____3yr Rank____1yr Return____3yr Return
ACEGX____84__________86__________0.67__________4.03

What I want to be able to do is have the Stock Symbol in a certain column, and return the other information from the Internet. I use smartmoney.com for looking up the info currently. BTW, the Rank is "% Rank in Class".

Is this even possible? I've only seen people get Stock Prices, and that comes from only from one source. Am I doomed to take 1.5 hours each week updating info that Excel should be able to perform for me in less than 30 seconds?

Any help would be GREATLY appreciated.

JonB

[Carim]

Hi,

What about getting yahoo quotes with following code

Please Login or Register  to view this content.

HTH
Carim

[JonB]

Thank you! I had NO IDEA that Yahoo Finance had the Rank in Class. I'll give it a try!

Thanks again!

JonB

[Carim]

You are more than welcome ...

Have Fun ... and Profits ... !!!

Carim

[JonB]

Um, ok. Now my noob status is showing.

Where do I get this Yahoo Quotes stuff? Is it an add-in for Excel? And where do I put this code? And, lastly, is there a tutorial on using it?

I've used Excel to do LOTS of calculations before, but NEVER something like this.

Thanks for all your help!

JonB

[Carim]

Hi JonB,

A couple of remarks ...
1. This is not an add-in, it is a simple macro to be saved in a standard module
2. It retrieves prices and volume info, as lon as stock symbols are located in column A
3. It does not bring back rankings ... this calculation would have to done at the worksheet level ...

HTH
Cheers
Carim

[JonB]

Ok, you're getting me on the right track. One step closer to my objective.

I got the macro to work, but it only gives me the current net asset value, date, time, and daily change. If I was doing calculations using these numbers I'd be in heaven!! However, what I'm looking for is the 1yr, 3yr, and 5yr returns and corresponding % rank in category. These are completely different numbers, and can't be calculated off of the info returned.

I COULD calculate an individual funds % Rank in Category, but that would require me to download every mutual fund in a specific category (probably 1000 different funds) and perform the calculation based on each funds rate of return for the specified time grouping, again, using different data than what was returned in the macro.

I looked around on Yahoo Finance's site, but didn't see anything on how to get the information from the "PERFORMANCE" screen into a spreadsheet. There IS the ability to get data from other screens into a spreadsheet, but just not that one. Maybe I'll have to ask Yahoo! if there's a way to get the specific data I need into my Excel spreadsheet...

There MUST be a way to get this data, I CAN'T be the only person who uses this. You know, maybe there is a way to get ALL of the data Yahoo Finance returns, I could pick through it and pull out what I need. Don't really need to be specific on what I get, just on what I USE.

Any thoughts/ideas?

JonB

[Carim]

Hi,

The only solution is to dig into the Yahoo Finance site until you can get your hands on the specific fields you need ...
DO not hesitate to drop them a note... they will give you a hand ...

HTH
Carim