Return web site data using Excel and VBA

[Tenspeed39355]

Hi guys I would like to look into a web site and retreive one line of data.
Here is how I would like to do it. Lets say that I have a column of stock symbols in column A. 1 thru 100. I would like to start with A2.the symbol is
GOF.The next symbol is PHF, the next is CYE. The web site that has the data
is CEFConnect.com. The data is on the second page under dividends.
The data to retreive is like the following.
GOF 05/03/2010 05/28/2010 05/12/2010 $0.1540 $0.13300 $0.0210
The data would start in column B, C, D, E, F, G, H
The next line would be the following.
PHF 5/14/2010 5/31/2010 5/24/2010 $0.0600 $0.060000
It looks like some sort of loop. The would be done until all 100 symbols have
the data. Is this posable??
Thanks for your time with this one.
Max

[Shred Dude]

I'd probably go about it with a simple XMLHTTP request to each page and then strip out the pieces I wanted.

Attached is a rough example. You could beef it up to suit your needs.

click the button on the sheet, highlight the symbols to retrieve data for and it'll do the rest, assuming you have an active internet connection.

[mdmackillop]

Very nice.

[Tenspeed39355]

Hi Dude. Great work. That works like a charm. One question. What do I change to get your program to include
650 symbols. If you can tell me what to change I think I can do it, or do you want to send me another example
Thanks for your time with this
Max

[Shred Dude]

I used the Highlight the symbols approach as a quick way to identify which ones to retrieve. You could just drag down 650 rows in that column and let it rip.

I'd probably consider consolidating the loop into one routine so you don't have to create and destroy the XMLHTTP object 650 times. Then you could just cycle through the symbols, rebuilding the URL each time. Might pick up a little performance.

You could eliminate the need to highlight the symbols if you knew you always wanted to just do the whole column for example. You could create a dynamic named range for the symbols column and then use that range in the code as the source of symbols.


To use this in an ongoing basis, your'e going to want to add the DeleteURlCacheEntry function so that when you go to do it the next month you get the new results, and not the results that might still be cached from the last time you ran it.

Add this is at the top of the module:

[VBA]
Public Declare Function DeleteUrlCacheEntry Lib "Wininet.dll" _
Alias "DeleteUrlCacheEntryA" _
(ByVal lpszUrlName As String) As Long[/VBA]

And then add this line in the getData routine right after you create the URL:

[VBA]DeleteUrlCacheEntry(strURL)[/VBA]

Otherwise, the XMLHTTP request will use the results previously retrieved from the URL, instead of doing a fresh query on the website.

Good Luck with it.

[Tenspeed39355]

Good morning. I was waiting for an E-mail on your reply.
When I did not get it I came back over here. No problem.
One item I do not understand. You said to put the following after the getdata.
DeleteUrlCacheEntry(strURL) I do not know where to put it so can you explain where it goes? I know little about VBA. I did make a copy of your code. That works. Please show me where it insert the new code line. Thanks Max

[Tenspeed39355]

Hi Dude. I have not heard from you on my last post. I do not know where to insert the data so can you help me out with that. Also your VBA program is limiting me to 17 symbols. Can I enter 650 symbols at one time? Thanks for your help.
Max

[Shred Dude]

Here you go. Will do up to 1000 symbols in column A starting with A3. Can't have blanks in the list in Column A. Symbols not found will Return "Not Found".

click button to kick it off. Monitor progress on Status Bar at Bottom of Excel Window.

[Tenspeed39355]

Mr. Dude Thanks for your reply. I have one thing to say.
YOU ARE THE MAN. Thank you for your time with this
problem. When I first posed the question I thought the following. Well I have them now. There is no way to do what I was asking. What a surprise when I saw your post.
Again thanks. By the way I will give you the highest rating. *************+. LOL
Max

[Tenspeed39355]

Hi Dude I have looked over the web for the answer but I cannot find what I need so here goes. I am in Sheet2 is blank and A2 is highlighted. I want to go to Sheet1 and find the Maximum value of G2:G24. The return is a percent. I want to bring that number and the Symbol from sheet1! A2:G24 back to A2 and B2. I would like the symbol to be in Sheet2 A2 and the number in Sheet2 B2.
Can you help me with this. I have tried using the Max, match commands but no good. Thanks again for your help. By the way I gave the previous problem five stars.
Max

[Shred Dude]

You should start a new thread. Pose the question there.

I'll be glad to have a look.