Excel VBA - Get Stock Quotes from Yahoo Finance API

how's it going and today Satori I wanted to show you how to make an Excel program that downloads stock information this was requested in one of my videos in the comments and so what I did here is I made a column where you input the symbols for the different stocks Google Apple IBM and so on and when you click the refresh button you get the name of the stock the current trading price the high of the day and the low of the day so I click refresh and it all gets populated and you can add new ones and it'll detect the last row and automatically update everything in there so I'll go ahead and recreate this one from scratch symbol name price high-low and we need some symbols in there okay I'm gonna format this as a table make it look pretty I'm not sure why that happened oh I forgot the check my table has headers there we go and go to the Developer tab and if you don't have the Developer tab go to excel options click on popular at the top if it's not there already and then check show Developer tab in the ribbon right here we're gonna go to insert ActiveX controls command button and I'm gonna click this properties button and I'm going to name it button BTN refresh I'm gonna give it a caption of refresh and we're gonna change the colors alright you double click on it to go to the code window alright so first of all we need to go through and collect all these symbols but actually first let me show you the actual Yahoo Finance API I'm using so I googled and I found that Yahoo has this API where you put in this URL and you put in the stock quote symbols and you separate them by a plus character and then you've got a and F and there's a bunch of different arguments you can put in there Flags whatever you want to call them for things you want back for example a symbol the name of the stock and the last trading price and so on so to get all these symbols we're gonna have to loop through all these all the rows here until the end so I'll show you how to do that if you haven't seen this semicolon here before it's essentially the same thing is going to the next line I like to have the sets on the same line as the as the variable so it's all clean to get the last row I'm gonna create a integer variable I'm gonna call it last so the way to get the last row in Excel if you click on a cell and you tap the end key and then you press any arrow key up down left or right it'll take you to the next cell that has data in it so if I click up or tap up it takes me to row 6 then say our we're in O one and I hit end left you know take me to the first cell with data in it so I'm going to use that method to get this last row number here and that's what I did here I said range a 1000 the 1000 could be anything you want just as long as it's gonna be at the bottom of your data if you're gonna have more than a thousand stock quotes in there and then you're gonna want to use 10,000 50,000 whatever you need and dot and exhale up row so if I print this out to our debug window debug print glass and press ctrl-g to bring up this immediate window or you can also go to view immediate window I'm gonna press f5 or and click play and you can see that kicked out 6 and that is our last row there so we're gonna go through and get all those stocks so I'm going to create a string called symbols and integer to go through the rows high for I equals 2 to last next I before we get to this point though you want to make sure that there are actual symbols in there and say if there if if this whole thing was empty then when I hit end up it's gonna take me to a 1 which is just the header so we know that there's no symbols and started in there and the program can't run if there's nothing to look up so just put in here if last equals 1 then the exits up so here are many symbols equals symbols and W dot range a and I value and plus because these have to be separated by that plus character so let's see if we get so you can see down here it got all the symbols and and separated them by a plus character we have one at the end there which would be a problem and that's easy to fix by just saying symbols equals left symbol the length of symbols minus one and there you go so if you have R symbols all right so the next thing we're gonna do is create the URL and let me get that taste that here and our symbols and ampersand f equals and now we're going to given all the different columns we want which are going to be name price high-low right so I've already looked this up but the ones I'm gonna want to use are s and L 1 H G let me print that out alright so there's my URL it should have my information let's put it in the browser just to make sure it's the right information so the CSV box file downloaded and it has all of our information so what we have to do now is you use Excel to download this information and then put it into our chart the way we want it so the way you can download this data is using the win HTTP object which you go to tools and the VBA window go to references scroll lay down a Microsoft win HTTP there it is some would say HTTP as new when HTTP request I'm gonna say HTTP dot open for the method here a string get URL pass in the URL and async I'm gonna say false which maintains synchronous you set that to false so that the code doesn't progress to the next line until that has already completed and then said and let's see we get back HTTP dot response text there we go so this was just a string now we're gonna have to go through this line by line and pick out the parts and populate our spreadsheet I'm gonna put it in a string so we're gonna go want to go line by line and the way that you can take a bunch of lines and then go through and one by one is using the split function so gin lines as variant lines equals split rest and then the delimiter is you're telling it how you want to break up the string say if there were spaces and you wanted to get all the words and sentence split up then you would split it by the spaces so hello world for example has a space between hello and world so if you split it by the space you're gonna have the first item being hello in the second being world but in this case we want to split up the lines so you can use VB crlf as the splitter you can also which stands for carriage return line feed or you could also use a BB newline so if I say debug print you bound which stands for upper bound lines this should tell us what number the last line is five so it's saying that we have five well it's actually six elements and this what would you call an array it starts at zero so the first one is zero second one's one and so on so it can be a little confusing for I equals zero to upper bound lines next I I'll say S line equals lines I so now I got it broken up line by line at a time so if I print this out to bug got print s line I'll say stop so we can see what it's actually printing out you never play this see there's one two three four five six there's actually an empty line at the end and I could show you how we can make sure that doesn't break our program later so we have to do is some string manipulation here you can see that every stock is divided by a comma CSV comma separated values and the text values are inside of quotes here and then all of our numerical values are just split by a comma so we already have the first one the symbols cuz they're already in our spreadsheet there now we need to get the name which is the second one here so if we used to split again values equal split s line comment stop all right so now values is holding IBM in international biz and then the rest of these numbers so I can also show that to you visually if I right click on values and go down to add watch click OK here and the watch window comes up here and if I expand values you can see that zero IBM one name to price three high for low if I came down to the debugger window and I put value say two or values one I get I get the business name so for our worksheet we want to put it in the second column W dot cells for the row we're gonna start it too so I'm gonna take I which is the first one's gonna be at zero so you're gonna need to add to 0 plus 2 is 2 column 2 dot value equals values 1 now I'm gonna get rid of that stop all right so you can see it populated our stock names there and I'll show you how to get rid of the quotes and there was an error there which I'll explain so like I said these stocks are divided by lines and we split them up by the line here but the very last line is just empty so when you try and split the last line by Paulo no there is no comma and that's why we're getting an error so so here we could just say if inside of the string in store s line we have a comma and it's greater than 0 which will tell you the position of the comma then we have we have something that we can work with and if there is no comma in your line this instr function will return at 0 so that's how we know that there's no comma there so I should be able to run this without an error now there we go and we're gonna want to get rid of those quotes which you can use replace and I happen to know that the quote is a character 34 so just put char 34 find char 34 and replace it with nothing so just do a double quote there and run this again okay I've got rid of air quotes now we're going to want to get the price which is the zero one two value and we're gonna go to column three values to fresh all right there we go now format these columns as currency and now we're gonna get the high which is values three and a low which is values for refresh there we go and one of the thing you can do is allow it to autofit the columns so it automatically resizes the columns intelligently W cells columns Auto fit so that way if your columns are stretched out or shrunk too much it'll fix it for you so I stumbled across the problem though some of the stock names actually have a comment and their name for example if I put Facebook in there they actually have a comment on their name I hit refresh see it's screwed up now Facebook went to name ain't because it's Facebook comma aim went to price and this is the this is actual the actual trading price this is the high and the low is not even on there so forgot to do something different for that one so you can see here with the Facebook one instead of splitting by this comma as we have here for the values value split the line by the commas we can split it by quote comma quote or we could split it by yeah let's do the quote comma quote so for example if I said split s line chart 34 and comma and char 34 and I want this first value let's check it FB get the second value so I have the name here and what I can do now is split it by that quote split this entire line by a quote and then just get the first value so what I mean by that is split this one again by the quote and get the first value so now let's see if says Facebook Inc and it's not split up so you might have to watch that a few times I know that's that can be confusing especially if you're neo but so I'm going to put that here split s line by char 34 and a comma and I charge 34 one and we're gonna split this by a chart 34 and get one and that should be it I'm sorry zero all right so now our facebooking showed up right what's going on there oh yeah so in this case what we could do it's still messed up here got the face looking to show up right but these are offset now because there's more commas and all these other ones all the other stock quotes these have they don't have a comma in their name so it's not zero one two three four five it's zero one two three four so what we could do is instead of saying - we could get the upper bound of the values the values upper bound values - one two and the same thing for these last two except it's gonna be minus 1 and then here it's going to be values around the values and there we go now they're showing up correctly even if they have a comma in their name so that's the quick and dirty version hopefully that was you guys were able to follow that if you have any questions feel free to post them in the comments if you guys have any requests for tutorials please let me know and I appreciate you watching thanks

Channel: DontFretBrett

Views: 479,229

Keywords: Microsoft Excel (Software), excel, vba, strings, split, winhttp, automation, stocks, yahoo, api, finance, trading, portfolio, brettdotnet, wontfretbrett

Id: iSlBE3CWg5Q

Length: 21min 49sec (1309 seconds)

Published: Fri Nov 30 2012