Retrieving Stock Prices in Excel With Macros (or any other web data)

Excel macros

If you want to learn Excel, keep reading or Get 10 days of free unlimited access to Lynda.com. for professional help and Excel Tutorials
***************

Last week, we built a very simple spreadsheet that allowed us to get the last price of Microsoft’s stock. That can certainly prove very useful but it makes it difficult to gather larger quantities of data. If you were looking for stock prices of 5, 10, 20 or even 50 stocks, what would you? Have 50 different queries running all at once that would get the data. It would certainly not be very efficient. Instead, we can use macros to achieve the same result. I will try to get it done in a simple and clear way to help you understand. I will go line by which should help you understand how it is done. To get started, I created a simple excel file with 2 different tabs.

1st Tab = Ticker List
2nd Tab = Web Query

First off, I will start by creating a macro that goes to the “tickers” tab and reads each ticker. Here is what I get:

As you can see, I have a variable named “I”, this simply replaces the line where I start. For example, i=5 to start off. So when I start, I tell the macro to start at the cells 5,2 (5th line, 2nd row) and move down until that cell is empty. That way, the macro will be able to read the tickers one at a time until there are none. The macro will do the parts between “Do until” and “Loop” until it gets to that condition. Of course, for every ticker, we need to do a web query right? So let’s add a big part that looks scary but really isn’t:

I know, it looks complicated. But you really do have almost nothing to modify here. The only thing we need to change is the “URL” as MSFT will not always be the ticker we are looking for. We do have a variable for that. So let’s use that instead. I will replace:

http://finance.yahoo.com/q?s=MSFT&ql=0

With:

http://finance.yahoo.com/q?s=” & ticker & “&ql=0”

That way, I will be do a query for each ticker. The missing part is to capture that price. To do so, I had added a vlookup under the tickers. I will thus simply get the macro take the result of that vlookup and put it in the right cell. Here is what I have before doing this:

Now, I will simply add a simple operation:

Cells(i, 3).Value = Application.VLookup(“Last Trade:”, Sheets(“WEB”).Range(“A1:B1000”), 2, False)

The final result is:

When I run the macro, here is the result:

Clearly, the code could be more optimal, faster, etc. But I think it remains a quick and easy way to get it done. You can download our spreadsheet here and be sure to check our intro of excel macros if you have questions!

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

24 Feedbacks on "Retrieving Stock Prices in Excel With Macros (or any other web data)"

Frederic

Hello,
I applied the macro code you provided to retrieve stocks in Excel. The macro runs the successive operations well till the end but I get #N/A all over instead of stock prices. Those #N/A appear at the correct locations, so it means I properly changed the i value and the row numbers in your original code. But the code does not seem to retrieve the stock quote info from the web.
In the part of the code devoted to the WEB tab, I did not change anything. Any idea what I’m doing wrong?
Thank you for your help!
Frederic



Frederic

It’s Frederic again, still with the same issue as mentioned above.
I think the issue comes from the very last part of the macro code related to the Vlookup function:
Cells(i, 3).Value = Application.VLookup(“Last Trade:”, Sheets(“WEB”).Range(“A1:B1000”), 2, False)

When I dig into the details of your WEB tab, I don’t see any part called “Last trade”, which is where, I suppose, the macro should get the last trade price of the stock, based on your VLookup function above.

On the WEB tab, the only place where the Last trade info is shown is on line 142. But:
1) it’s not only a simple figure (there is a figure + a whole bunch of info)
2) the whole stuff appears in column 1, not 2
3) the term “Last Trade” is not used at all

So it seems the problem is here but I still don’t know how to solve it! :-)
Thanks again for your help!
Frederic



tiong bahru condo

Hello there, You have done a great job. I’ll definitely digg it and personally suggest to my friends.
I am sure they’ll be benefited from this site.



Www.Touzes.Tv

Article vraiment plaisant



alexis silver

Je prends la peine d’



film hardcore en streaming

C’est un vrai plaisir de regarder votre poste



suceuse black

Tout ces post sont sinc



Plancam Gratuit

Un post rempli dde bons conseils



jeune cochonne

Je souhaiterai vous dire que c’est vraiment ddu bonheur de venir sur votre site
internet



salopes blanches

Voous nous concoctez sans cesse des postes int



salopes asiatiques

Magnifique article colmme d’habitude



petites putes

Magnifique poste, une fois de plus



femme fontaine mature

Une fois de plus un post sinc



whooty

Je remarque imm



rousse sexy

Excelldnt article, encore une fois



salope coquine

Quel plaisir de lire votre blog



sexe gonzo

Un gigantesque remerciement



salope blondasse

J’ai point fini dee regarder par contre je reviendrai demain



asiatique xxx

Je suis press



salope blonde

Un article raiment plein de bon sens



africaine chaude

Vous fa



film sexy gratuit

Une fois de plus un magnifique article : jje pense enn discuter demain avec certains de mes voisins



blackette

Ces posts sont v



monsieur glisse

Good answer back in return of this difficulty with real arguments and
telling the whole thing regarding that.



Comments

Please Leave a Comment!





Please note: Comments may be moderated. It may take a while for them to show on the page.