How To Get Live Stock and ETF Data in Excel 365

In this video, I'll show you how you can get

live

stock

and ETF price

data

in your Excel 365 spreadsheet. Excel is probably the most useful tool for investors and traders, and Microsoft has made it a little easier for us to get

live

prices and other company and exchange-traded fund information in our spreadsheets. I'm going to demonstrate using the spreadsheet on the screen right now how we can do it, so I'm going to put a list of some

stock

s and in my portfolio here I'm going to put Twitter, Amazon, IBM, Google, Oracle and Ford and Pfizer. The next thing I need to do is tell the spreadsheet that this is stock type

data

, so I'm going to highlight it.

I'm going to go to the data tab and click. Actions and we see that they are all recognized as actions. I have a couple that aren't quite right here. I put Ford here and it autofilled as Forward Industries. I'm just going to change that to F Ford. ticker and now you get the correct action. If I want to add more to this, I can do so so I can log into JP Morgan Bank of America again. We need to make sure that it recognizes the correct stock so that we have a selection of stocks to Now I represent a portfolio, what price data or what data might we want?

Well I'm going to say open high, low, close, volume change, what about price gains too? So I can do this by highlighting the actions and clicking here and then just choosing what I want then I want to open. The next thing I want is the high and I want the low and I want the close, which is the same as saying the price because of course that will change when the markets are open. I want the most recent change I want. the volume and finally let's take a look at the price gains so we have a table and as long as the markets are open this information will be updated, the information is taken from Morningstar and it is pretty close to the market, not close enough to negotiate, but it is fairly close and precise information.

Now what we can do here to make this a little more usable is convert it or change it to a table, so I'll click on Insert Table and now we'll have a table. Before we do anything more about this, I'll show you how we could do exactly the same thing with ETFs. Now you can trade ETFs, you can invest in ETFs or you may just be interested because they give you an idea of ​​a wide range of market I have some ETFs here, click on data, click on stocks again and you have correctly identified all of these ETFs.

Now all I want to do is take some of this data, so for my ETFs, I'm literally going to copy and paste. the tables I entered above and because I copied and pasted them, I can do the exact same thing with the actual data and highlight this, double click, highlight all, click insert table and there I have it in a matter of minutes. I have two tables, one showing my stock portfolio and the other showing my ETF portfolio. Now, of course, since we have this as a table, we can filter and sort it so that we can know if we are looking for the value that we want to know. what's the lowest of our price earnings you can see here, it's Ford Pfizer and Bank of America and what's the highest, it's not too surprising to see that Amazon.com is 92 in price earnings almost 92 now for the ETFs , we can have a look at what the most recent change is, of course, the opening close, high, low data, we could also take a look at a few different things.

I can add some new fields, so if I highlight this again, click on my tab here, what about the 55 week minimum? and the 55 week high and now you can see that it is automatically adding this data, including the title, to my table, so if we have this we can calculate a 52 week stochastic, which is a useful way to find out the relative strength of the different ETFs I can show that now and this would be the current price minus the low, put everything in parentheses and divide it by the current high minus the low, close the parentheses.

I'm going to change this to the 52 week stochastic, you can see here. now I have a measure of relative strength and this is very useful in case you're looking if you want to invest in strength you want to invest in weakness you're looking for momentum you're looking for what area what market is performing best so I If we can reorder this from low to high, we can see that the FTSE developed market is extremely weak at the moment and the best performer is QQQ's Tech after the Nasdaq-100, what else can we do now? We have this in a table that we can enter. our table tools, you may want to change the style.

Other things we can do. We can insert a slicer here so we can place this here, since this is the first action. I took a look at the stocks. I just wanted to take a look at Amazon. Alphabet Amazon thought it wanted to have, but in another here, another segmentation. I go into the table tools layout insert slicer and I want to select it by ETF. I have another one here, so if I just want to take a look at the tails or the S&P and if we had a lot more data, this would be a useful way to drill down into the chart and understand what it's telling us, but this makes a great daily dashboard that you can view at a glance from your own desktop in your own format. exactly what information what market information would you like to see if you enjoyed this video and would like to learn more about using Excel to analyze your trading and investment strategies to backtest your trading strategies and also to manage your trading strategies, press like. button subscribe to this channel to learn more about how to trade the financial markets using Excel, visit www.tradinformed.com