hi, this is yi from excelclout.com. in thisvideo, i want to share with you guys a really handy excel file i created to get stock quotesfrom yahoo finance. now first of all this is not a tutorial onhow to get stock quotes from yahoo finance. the file that i'm about to show you comeswith that functionality built in, so you can start tracking your stock investments in excelright away.
apple share value today, so let's get started. this is the link whereyou can download that file, or you can get there by googling excel clout (c-l-o-u-t)stock quotes. and it should be the first link in the search result. let's click on downloadand open the file. now, the first time you open this file, youmay get this protected view warning because
the file was downloaded from the internet.we'll need to click on enable editing. and this file contains macros, so we need to enablethat by clicking enable content. so right away you see this portfolio template.now if you're someone who's looking for a really easy way to track your stock investmentsin excel without having to write any vba code or even formulas. this the file you're lookingfor. and i'm going to show you how to use this file. so the convention here is: the light yellowcells ,over here, are the cells where you enter your stock symbols and quantities, andall the other cells and charts will update automatically based on the information enteredin these light yellow cells.
the pie chart shows you your allocation, andthe bar chart shows you your profit and loss for the day. the table over here contains information suchas the price, which is the quote from yahoo finance, the change in price since last closed,the percentage change, the profit and lost, and the total value for each stock symbolyou've entered. so let's play around with this table. let'ssay you own yahoo stocks instead of google stocks, so i'm going to change the goog, whichis the stock symbol for google, to yhoo, which is the stock symbol for yahoo. and you seeright away the charts and numbers are automatically updated. now let's change the quantity 12to 30. and again, the charts and numbers are
automatically updated. if your portfolio contains fewer stocks, youcan always delete any extra rows that you don't need. so let's get rid of these threerows by first selecting them. right click and delete. if your portfolio has more stocks, you canalways insert rows into this table. so let's select any row that crosses through this table.right click and insert. now suppose you own 50 shares of coca-cola.and the stock symbol for coca-cola is ko. and you have 50 shares. now to refresh the information on this sheet,including stock quotes and price changes,
just click on the refresh button over here,and you've noticed the numbers have changed. you can also turn on auto refresh by clickingthis auto refresh every minute button, which automatically refreshes the quotes every minuteat the first second of each minute. and of course you have the option to disable thatfeature by clicking the stop auto refresh button. so no need to code or even enter any formulasto get stock quotes and track your stock investments in excel. now if you're a more hands-on person and you'dlike to put together your own stock analytics dashboard, you may want to check out the examplesheet. so let's go to the example sheet.
the example sheet basically contains exampleuses and documentation for all custom-built formulas that come with this file. so over here, you see that there are sevencustom-built formulas. for example you have stock quote, which returnsthe delayed stock quote for a given stock symbol from yahoo finance.stock change, which returns the change in stock price since last closed.you can pretty much figure out what each formula does based on its name. you have other formulaslike stock day's low, stock day's high, stock year low, stock year high, and stock volume. the table on the left basically uses all sevenformulas. to give you an idea, if you look
at the first row, amzn, the stock symbol foramazon, under the quote column, and if you pay attention to the formula bar, it saysequal stock quote b10. and if you look the the next column, the change column, the formulabar has equal stock change b10. so you get the idea. one last note: when you're building your ownstock analytics, you can copy and paste these three buttons - the refresh, the auto refreshevery minute, and stop auto refresh buttons - to your new sheet within this file, andthey will work just fine. so that's it! i hope you guys find this exceltool useful. and again, this is where you can downloadthat file.
i've also put together other free tools youmay find useful. and you can find them on my free tools page. so i really like to know what you guys thinkabout this tool, what you like or don't like about it. and if you have ideas for otherexcel tools, feel free to leave me a comment. hope you guys enjoyed the video!
