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
***************************************************
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.
Harrison Delfino
MarketXLS works for me just great for this.
Spinal Decompression Culver City
Spinal Decompression Culver City
Retrieving Stock Prices in Excel With Macros (or any other web data) | Experiments in Finance
Culver City Chiropractor
Culver City Custom Orthotics
Retrieving Stock Prices in Excel With Macros (or any other web data) | Experiments in Finance
Custom Orthotics Culver City
Chiropractic Clinic Culver City
Retrieving Stock Prices in Excel With Macros (or any other web data) | Experiments in Finance
Massage Therapy Culver City
Culver City Chiropractor
Retrieving Stock Prices in Excel With Macros (or any other web data) | Experiments in Finance