Excel VBA - Get Stock Quotes from Yahoo Finance API
Vložit
- čas přidán 5. 07. 2024
- / freetutorials
*Edit this no longer works due to Yahoo abruptly decommissioning their stock API. Still worth a watch if you want to learn how to how to interact with an API in excel and some string manipulation techniques
In this tutorial, I show you how to use Yahoo's finance API to download real time stock quote information. This tutorial utilizes a for loop, string manipulation / concatenation, the WinHTTP COM object, and the split string function.
Since posterous closed: brettdotnet.wordpress.com/
BTW. If your work blocks WinHTTP through the firewall use XMLHTTP
Workbook (updated api URL 6/26/2017)
www.dropbox.com/s/aihc2nfhlyl...
New URL:
Dim URL As String: URL = "download.finance.yahoo.com/d/q..." & Symbols & "&f=snl1hg"
Update this line to this:
Dim Lines As Variant: Lines = Split(Resp, Chr(10))
Thank you for the download quotes, really appreciate your time in making this video. First time VBA user here and I could follow and modify your program to fit my needs. Your video technique allowed for people of limited experience to pause, review and make the necessary changes; the interim "test" checks you used were invaluable. Well done.
This is such an awesome tutorial. It really opened my eyes to the possibility of accessing the vast financial data from the web and using it in excel. Also enjoyed the general VBA programming tips and tricks and you went through. Thank you so much for sharing! And the pace was just perfect. (of course, I had to stop and go back as I went thru but that is normal).
I know zilch about any forms of programming, but wanted this exact type of solution to updating my financial spreadsheets. Your elegant solution helped with my laziness and your instructions were spot on. Thanks and thanks again!
Thanks for the Video. As someone who is new to VBA, you showed alot of great tips! Appreciate the explaination and moving along so quickly. I'll be watching this video a few times to make sure I get everything :)
As a complete newb, I was able to use your video to successfully (finally) get it working perfectly with our stock stop/loss modelling efforts. THANK YOU!!!! (had to use the CHR(10) recommendation made below)
Thank you so much. I know little enough that I would never figure out how to do this on my own. I appreciate that you shared your knowledge. In a way its a good thing that the link to the code didn't work anymore. This way I learned a little more by plunking it in my self. Thanks again, Doug.
Brett I have watched a few of your videos the last couple of days. you are great man! Thanks for teaching us. You just earned my subscribe!!
wow, I have to admit, this was probably the best vba video I've seen on youtube. great job!
Brilliant - I'm new to this but this is best video I have looked at so far - and I've looked at a lot!
info was great, pace was excellent (rewind/pause worked well), and great explanations of almost all aspects. Thank you!!
I just want to thank you for this, i used it to build an uber spreadsheet to track all my investments. I had literally never written anything like this before (although I'm pretty competent in Excel generally) but you did such a great job explaining that I figured it out. Thanks. I also recommend Activetick for anyone who wants tick by tick real-time, but it only does stocks/etfs, no mutual funds, so you'll still need yahoo api for that.
Thanks man that was great! My friend showed me this a while back; he loved it too. Best Regards, Mat
Awesome tutorial with great explanations. I have 0 experience with VBA but was able to follow all the way to the end.
Thanks!
Awesome. Thank you. I downloaded your sheet from dropbox and was able to add columns for other data. Thank you once again.
Was great that you showed the end result up front. Thank you!
Great job and thanks for sharing. It is great to watch someone else build code from scratch. I am sure this has helped many clear up the logical process.
Thank you for a phenomenally useful and well delivered "lesson." Following are a couple of tweaks that users might consider and possibly benefit from. I understand that in the interest of time you had to just go with expedient choices since the video is already long as it is. Anyway your delivery is quite efficient. The zooming is extremely well employed and aids viewer comprehension immensely. I wish someone would teach me how you built this video. Also, your very time efficient visits to debug.print and watch window, use of Stop, and testing the URL manually, are all exceptionally fine instructive techniques. Success.
Tweaks:
- The reference can be omitted and your http dim can be replaced with
dim http as object
and you would add this line before the "GET"
set http=CreateObject("MSXML2.XMLHTTP") 'for XP
(MSXML3 or MSXML6 may be needed. Check SYSTEM32 to see which you have.)
- After the first VbLF split, I would not use split to break up the individual comma separated rows for each security. I would just use
columns(1).TextToColumns Destination:=range("A1"), comma:=true
That handles every column, although one might want to break up the dual change values (amount and percent) when using flag c. It also automatically addresses commas within quote-delimited strings. (Sorry, but the comma approach was not your proudest moment in the vid!)
- Autofit can be a tiny bit annoying from fluctuations like 9.94 becoming 10.02 so I prefer to lock down the column widths (or only grow them).
- The worksheet qualifier is unnecessary, as well as W itself.
These are small tweaks. Great video. Great presentation.
+Bill Woo I am really interested in your tweak using the following line:
Columns(1).TextToColumns Destination:=Range("A1"), comma:=True
I placed that line directly below the line
Lines = Split(Resp, Chr(10))
It appears that your line of code should place the data in the worksheet starting at cell A1 but I do not have data on my sheet after running that line...how would I get the 'TextToColumns' data on my worksheet? I appreciate your help!!!
+Dan Kordelski Use something like
for i = 0 to ubound(lines)
cells(i+1,1)=lines(i)
next
You're correct, lines needs to be placed on the sheet first.
This was of a great help, thank you so much!! (if fact this was my first VBA code, and the video helped me to get a first touch on it )
hey my dude
you handled this code like I handle my money - A LOT
Sorry! I figured I'll go fast and people can pause / rewatch if they want. I've watched tutorials before and thought for the love of god, I get it!! Move on :)
I thought this was well paced. That's why there's a Pause button (h/t Google).
Thanks for putting this together!
+DontFretBrett Great Tutorial! It was SO helpful to what you use Stop, debug.print, and the intermediate window to explain and troubleshoot. Totally worth the price of admission!!!
This is a perfect pace. Please don't slow down. People can slow down the video using the gear icon and speed function in CZcams.
Wow, way to fast for me, and I had to stop, go back, restart repeatedly to see exactly what you did and to get my excel to replicate your results. Still, was thrilled until I realized it doesn't work any more.
thank you so much for the vid! although there were some issues, but the comments section helped me to solve it. thanks!!
Awesome video. Extremely helpful. Thanks!
Great work with the tutorial, Brett!
Hey DFB, thanks for sharing your idea. This is great stuff. Now that microsoft MSNBC stock connection is no longer working this perhaps is the best alternate route...Thanks again.
Great. it works. Learn a few things from your video.
Smooth de-bugging. Love it.
Excellent video !! Works well for me, thank you !
you just saved my life.
Thank you so much!
Thank you very much for this tutorial.....
nice work
Super Bro..............keep up good work , your teaching is so good,
Thanks. Excellent tutorial. I learned a lot (and I needed to do this).
Awesome tutorial. Works great :)
Thank you for the great instructional Video!
Brilliant!!! Just what I needed...
next stop - MATLAB!!!
Great tutorial. Thank you!!
Good news, even though the CSV interface has gone away, you can still get this data with the Yahoo JSON feeds, and its real time prices for many exchanges.
Excellent video! Thanks! Keep up the good work.
Very nice video and helpful.. Thank you :)
Dave, it allows 200 at a time, but it's easy to expand this out too as many as you want with a loop (loading 200 each time)
You're the man! Thanks
In office 2013, to split the lines, the split function should be written as Split(Resp, vbLf). I spent two hours and finally figured this out.
@NOAH Set is for objects, the variables without Set are functions (like SomeVariable = "SomeString")
Thank you... on so many levels!
...especially the clear explanations of each code, line-by-line -- extremely helpful.
Awesome tutorial!
Thank you :)
DontFretBrett Hi Brett, I wanted to let you know that as of today the download no longer works. This was also noted by another reader (jbarossi2). Please help us to resolve this and I thank you for taking the time to give us all tutorials that are very help.
You're the man. Thanks a ton.
Thanks for the good video.
Great video thanks!
it is very cool~ Great Job~
Thanks for the positive feedback guys. Someone asked about Mac, I don't know much about excel on Mac sorry. I know it can't use activex com objects like scripting runtime, but the syntax is very similar
Are you a software engineer?
I got a runtime error 9 after row 200. I'm using your exact code. Can you help?
I'm running a Windows VM on my Mac, just to be able to run this. Does not work on the Mac.
Hi can you share your mail address and contact number, Iam facing problems in a VBA script. want to discuss with you. If you want to keep your contact details private then please mail me here in bagchi.prakash81@gmail.com, I'll send you the script and required result.
***** It won't work on OSX because this uses windows specific commands. I was trying to find a way around it, but I ended just using my windows machine.
Superb Macro ... Thanks
Very nice, thank you.
Well Done, Very Well Done....
You are simply a genius! *.*
Awesome video Brett thanks! I just started VBA a week or so ago and this was incredibly helpful.
I was messing around with scraping BLS reports (non-farm payrolls) with PHP, but do you have any examples in VBA to maybe start a loop in VBA at 8:29:45AM EST, keep checking the report URL until it's been updated (i.e. VBA detect a "July" heading vs a "June" heading which it would ignore) and use some parsing to put the U-6 rate in a cell range?
Wow this is awesome
This is great, thank you for this video! My next question would be, how do I use this code to download historical prices from a CSV file for a particular stock symbol, and paste the output into a specific range on a pre-formatted worksheet within my workbook?
Thanks! infomrative source for reference.
Hey Brett. This post is genius. Thanks a bunch.
Do you know if there is a work around for a mac? I get stuck with the winhttp part.
Respect! thank you so much
You did an awesome job here! Do you have one to download historical data on multiple companies? I'm looking for dividend and dividend pay date.
Finally, a stock market tool, using VBA, that is clear as glass. Next time I see you I'm either going to hug your neck or buy you a beer; you'll probably prefer the beer. Many THANKS
well explained. good tutorial. thanks for sharing. i have a worksheet that when either of the sheet becomes active it messes up the other. is there a way to correct this using macro? for instance, i have sheet1 & sheet2. both have separate macros that when sheet1 is open or active, it execute the macro. verse versa.
Would be great if you could update this tutorial because it's a great one!
Brett
This is fantastic. I had never done any coding before but following your directions enabled me to build this sheet. Love it.
One tiny glitch...one of the attributes I import is dividend yield...the figure imports as an integer, so for example a 5% yield translates in excel as 500%. Any way to code in a fix (for example, divide value by 100) that would properly format this? Thank you in advance. Dan
This was very helpful! Just one question. If I wanted to add more columns (PE ratio, percentage change, etc...) how do you add the columns after inserting the symbols in the URL? Many thanks
I'm also having this issue.
This is fantastic!!! Exactly what I needed except could you show how to do the loop to do more than 200? Thanks for the great work.
I am having the same problem trying to loop for more than 200 stocks, did you have any luck finding a solution??
MarketXLS works for me great for this.
Hey there, this is pretty awesome, thanks for sharing. One questions only: Is it possible to import data/prices from other websites ?
is there an alternative for the yahoo API?
After some investigation, you need to change the split to "Split(Resp, vbLf)" then it should work.
Ashley Howell Thanks can you please part of the code we need to change ?
I changed to Lines = Split(Resp,vbLf) but doesn't seems to be suffisant
Ashley Howell you are a sir!
Ashley Howell super it works, thanks
Brett, thanks a million! I have been through a lot with web access techniques to stock prices... and I do have a couple of comments/questions:
Comments:
1): I was able to replicate your software up until the WinHttpRequest statement. I am (still) using Excel 2003 (why pay more? and again?), which threw an error (type not defined). I did go to the Tools->References and found the HTTP services, so I'm not sure... probably my old Excel?
2) For others who get this far with an oilder Excel, I used an alternative function I had found in my last week's research: Set Http = CreateObject("MSXML2.XMLHTTP") which replaces just the one line with the WinHttpRequest type.
3) Next, I got an error when I got to Http.Send. I had verified that the URL worked in a browser window and was puzzled for quite a while. Finally, playing with the browser test again (and again), I noticed that the return of the data also had modified the URL to include 'download' as in 'download.finance.yahoo.com/d/quotes.csv?s=" etc. This fixed my problem!
4) I added a number of "constants" (does visual basic have constants? I just used variables) to check the active sheet, to modify the position of the column and starting row for symbols, but that is all pretty obvious.
Question:
1) I wasn't able to get the cute Refresh button to work in Excel 2003. Consequently, I wonder how I will get the subroutine to run...I turned on the VisualBasic toolbar but when I hit the RunMacro button, I have to live with a popup window every time, telling me that it is about to run the only function I have defined... SIGH!
2) IF I can convert the subroutine to a Function (why not?), I can readily force it to rerun from the worksheet (and hope I don't get the popup each time)...
Any hints???
My history is interesting (to me at least)... Originally I was happy using the MSN Money Stock Quotes, but support finally disappeared. Then I found an example that still used MSN but was rather clumsy, providing an entire, large array of stock info I didn't want for my spreadsheets. And last week that MSN support died! I scarmbled to the web once again. I found a number of methods and picked an easy one that used yahoo's ichart query. It was slow, but seemed okay over the weekend, but then on Monday, I learned that it was pulling "historical" data and therefore did not provide today's prices, let alone up to date prices while the market is open... SIGH Again! Your solution looked promising, and inideed it is going to meet my needs!=) I could use hellp getting a 'Refresh' button working.
THANKS AGAIN!
Excellent, I like it.
Yeah, I know. Just use a do loop or for each and step 200. Have you looked into Regex to split those lines, then just load them into an array and parse the final value in one hit. That is what I did. Just some food for thought. BTW, I love your Facebook page.
great tutorial. It may take me some time to learn exactly what all the code does. I sometimes get a timeout or application error on the line http.send but if i run the macro immediately after I don't.
Great Post. Thanks,
What version of excel are you using?
You sure know your stuff - great job; Thanks. Is there anyway/anywhere that I can download the VBA code?; would like to get a quick start in customizing my own portfolio.
Thanks again
brett, great video, but is there a way to put categories on left side & symbols on top? thanks. bernie
Nice clip!
will do. thanx!
great video man. i had a problem opening up your spreadsheet with excel 2011 for mac it said activeX isn't supported. what do you recommend?
Can you please tell me your colors setup for each function? Yours are awesome!
Awesome!
Absolutely wonderful tutorial !! thx man :)
Are you willing to create one more on how to get historical data?
Would be over the moon, if I could also collect data from other dates than the current one :D :D :D
The excel book downloaded work very well. Thanks. May I ask if I can retrieve historical financials e.g. Rev, Gross Profit, Net Income, eps for all stocks for the past 4 to 8 quarters from Yahoo Finance? If yes, what is the command and the tags? Thanks
Hi Brett, you are so genius. but i i have problem. i follow all of your step, but i only can pull data on Row 2. next row still empty. can you give the solution?
thanks
For some reason the lines are not separating for me. I can get it to work on a single line, but the last columns has the number plus the what should be on the next row, in this case "USB" . I've checked my code against the tutorial code and it is identical. Any Ideas?
Hey this is fantastic and thanks for the tutorial. The only problem I am having is that it crashes if it has to loop over 150 rows or quotes. Any advice?
Nice one
is it possible for get the price for certain date not just today?
waiting for your helps ! thank you in advance
Hey DontfretBrett I really loved this video and the functioning model you had built. Which is true genius! Then yahoo just discontinued. :( Would look forward to your alternate solutions Mr. Genius. By any chance you would know if Microsoft has restored the excel connect to MSNBC stocks? That was discontinued as well few months back.
Found a very easy work around. Create an Excel stock spreadsheet with the same list of stocks you're watching or invested in. Create the identical Watchlist in Yahoo Finance. Export the Yahoo watchlist to a download file and open it. It will not be in Excel format, but that's ok. Copy the column of current prices from the Yahoo Quotes spreadsheet into your Excel spreadsheet and paste Links. Current prices will populate. Just did this, so don't know if I can automate the updates or if I must do it manually each time. Very new to this, so any help welcome.
Thanks a lot.
Without using refresh botton, how can I have the vb refresh automaticly by itself right after the excel is opened?
pretty cool !
Hi,
First, thank you very much for sharing this good video. I'm facing strange issue. I'm getting "N/A" for all values if my inputs are NSE listed stocks. Eg. LT.NS, MOTHERSUMI-EQ.NS etc. Any idea? Thank you.
Hello Brett, if I were to want to expound upon my chart and import other things from the gummy-stuff, how do I add more "special tags"?
Hello, very nice article simply, clear, well explain exactly what i want .. Good jobbbb :) Good continuation ..just a question is it possible to delete the button and a timer to refresh every 5 minutes for example ? if yes How ?! thanks
Hey Brett: Is there anywhere that we can see the entire VBA code? Thx. Excellent tutorial.
It is a great video and I like it so much.
I have one question, how don't I have WinHttpRequest when I write a line such as Dim Http As New WinHttpRequest. I don't see WinHttpRequest in my object. If I don't have the object, How do I add it up? Thanks.
Great job Brett ,try with bac+fb+vxx ,the fb name filed has seperated by coma i.e "facebook,inc" hence at this particular record ....price,high and low are misplaced and this record occupies another coloumn out of table format.
For mac user's winhttp services are not included, one has to use ActiveSheet.QueryTables.add(...). Thought it would help.