Microsoft Excel Hyperlink to Google Finance UPDATED #Finance

foreign hi and welcome to another Excel demo with Rich Kerr uh in this scenario we’re going to do or we’re going to revisit I should say a CashNews.co that we did a few years ago about using the hyperlink function to research company data uh at Google

href="https://cashnews.co/finance" style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance and I had someone ask is this doable in the web version and it is so I thought we’d revisit it but then we’re going to compare what you get out of Google

href="https://cashnews.co/finance" style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance with what you can get directly with excel’s special data types we’ll talk about that next and I’ll say at the outset this works in both the web version and the install

desktop version of excel you want to make sure you have the latest updates to have all this functionality so uh let’s let’s proceed so here let’s first talk about that Google none;">Finance query that you can run with the hyperlink function so I’ve got a list of ticker symbols for different companies in column A and in column B I’ll start in cell B2 and we’ll do equals hyperlink and then you uh you put your hyperlink text in a set of quotation

marks so it’s https colon slash slash and uh then we’ll do www.google.com forward slash Finance uh and then question mark the letter q and an equal symbol now this is the core

Google Finance search string so what you want to come after the equal sign is whatever you’re looking up within Google’s bold; color: #1a73e8; text-decoration: none;">Finance uh database so we’re going to attach with the and symbol we’ll attach the value from cell A2 so that’s going to query the ticker meta now we’ll put a comma here and we’ll use the optional feature or optional

parameter of the hyperlink function called friendly name where you can not show the full hyperlink in your cell just because it can be kind of long and heinous looking so you can just this is optional but you can put just a label for your link so we’ll say um we’ll say um research and

then and A2 close parenthesis so now it just says research meta and if I fill that down let’s do that again so we’ll go to that cell then we’ll drag that down to the other cell so now it says research and then whatever that ticker is so if I click research meta uh it opens up the

Google Finance browser and it’s within the browser and it’s showing us the lookup of meta within Google #1a73e8; text-decoration: none;">Finance you know go back to my Excel file I’ll do the next one so you can see it works just fine I looked up Apple look up lucid you get the idea so it gives you all that rich information uh that Google

style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance has on all the companies that are publicly traded so it’s very rich lots of info here for you to do your research now if you don’t want to do the look up to Google

style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance or that’s you know you want to incorporate that data into your spreadsheet finally uh Microsoft has given us the ability to do that uh if you go to the data Tab and this again is both in the web version and desktop

version you have the section for uh data types it’s what it’s labeled in the desktop version and the web version It’s just there on the ribbon uh more towards the left side of the ribbon when you’re on data so what you can do is you can you can select a range of cells and

tell Excel what special data type it contains and one of those options is stocks you see when you use it frequently they collect up here in the recently used but you should be able to find it in the library of options let’s go with stocks so it tries to interpret the tickers that you saw

you’ll also get a disclaimer that hey uh the state is as is there’s no guarantee of the accuracy of the data and that’s okay for for these purposes okay so it transformed what I had entered in column A into the special data type and now you can extract a variety of other data

points from that information it’s actually a very special type of formula component if I type equals in cell B2 and then click on A2 I’ll type a DOT and you get a drop down list almost like dot notation with objects and properties in a programming environment so with A2 as the reference

or the object then dot I can pick what was the 52-week high price or low price or what currency is it traded in which exchange is it on right so it tells me that it’s traded on NASDAQ so that I could call this column Exchange that will make that bold and I can pull that formula down to the

other rows as well these all happen to be NASDAQ traded stocks and so I’ll go over here and do another one we’ll say equals A2 Dot and I want to know what’s been the high price of this security over the past year and that is the value 353.83 and again I can drag that down now

we’ll call it uh 52 week high so you get some granular information it’s not all packaged up in a neat and tidy bow like you see on Google Finance but the benefit is that you can

sort of pull out these granular data points as you see fit and then construct your spreadsheet accordingly we’ll do one more let’s see um how many employees does that company have there you go so we’ll call this uh employee count and of course there’s my typo which

we’ll fix and we’ll bold that now the one difference that you’ll see with this functionality in the desktop version is when you click on the cell that you’ve converted into the stock data type oh actually there is no difference they have put the little card here too so when

I click in this cell notice this little properties card that pops out to the side so if I click it it also shows me the available options to choose from instead of you having to write the formula in an adjacent cell so let’s say I want to know the market cap that’s one of the properties

it also figures out that you’ve got additional data already to the side so it just goes to the next available open column and it puts in the market cap so that’s another bit of Simplicity that they’ve added to this is that you don’t even have to really write the formula in

that dot notation you just go to the cell in question click that little properties card and then pick the item that you want so we’ll say headquarters and you see it gives us the headquarters address now for meta so we’ll call this HQ so I could pull out you know those two data points

in that way and it’s still the same formula right it’s the the cell reference A2 Dot and then the property in question I’ll select both of those and then I will drag it down so this is a comparison of the hyperlink function taking you to Google

href="https://cashnews.co/finance" style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance which gives you a lot of data historical data you can change parameters once you’re there uh versus doing the special data type and uh pulling these specific data points out using

formulas so obviously this is something that you can incorporate into your spreadsheet whereas the Google Finance is just a link to Google’s

style="font-weight: bold; color: #1a73e8; text-decoration: none;">Finance page both can be useful depending on what your needs are I encourage you to check it out and again both are available in the desktop and web versions for the desktop what you want to do is make sure that you’re in

the um early adopter um subscription level and you can do that in the account options in Excel so you’ll go to your file Tab and go to account and make sure that you’re in the beta or early or early adopt I think it’s called beta subscription type and so you’ll have all

those functionalities if you’re in the beta level but you can do that with your subscription you just go and change it inside Excel I hope you found this useful please check in again soon for more Excel tips with Rich Kerr have a productive day