Retrieving A Stock Price In Excel

Excel function tutorials

Excel is very powerful, that is no secret, but many do not know that Excel can also communicate very well with other types of software. Want to send an email? Excel can communicate with outlook to get that done, it can also communicate with Access databases. One thing that I personally use a lot is gathering data from the internet through excel. It’s no surprise that Microsoft made it easy to gather data from the web as excel uses Internet Explorer to get the data. There are probably a billion different ways that this can be used but we’ll start with a financial use (of course!). There are a lot of reasons why an investor would like to know a stock’s price. Of course, if you own a stock, getting its current price will make it easy to know how much your investment is worth.

This is just one example but it can be done with almost any data that is available on the web. As we did with excel macros, I will start by doing a simple demo. Then, next week, I will give an example that will show you how to improve the speed, efficiency but also how you can use a macro to gather the prices of a dozen or more stocks.

You could use a large number of websites to gather financial data but we will use Yahoo Finance and for today we are looking to get Microsoft’s (MSFT) last price.

In any excel spreadsheet, you can import external data (either from a database, a webpage, etc). You can select the menu here:

Then, as if you were in a browser, you can enter “finance.yahoo.com” and press enter

Finally, simply enter the ticker “MSFT”, click “Get Quotes” as you would if you were simply visiting the page.

Then, you must select which part of this page you would like to extract, simply click on the arrow for that section. In this example, I will select the first one which would include all elements of the page. And then click “import”

You can then click on “ok” and the result will appear:

As you can see, you lose a lot of the formatting and images when you extract the information and in this case, since we selected the entire page, it makes it a bit difficult to read.

That being said, you can see the “Last Trade”. I could simply do a vlookup reference to easily see this page.

How Could I Refresh This Data?

There are many different ways. One would be to right click on the query and select “Refresh query”:

If you would like to take a look at my file, feel free to download it here.

***************************************************

Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss


***************************************************

2 Feedbacks on "Retrieving A Stock Price In Excel"

Asok Banerjee

Can you please give me the formula for the following problem; it will possibly require IF/AND/OR arguments. The problem is :
Calculate the amount payable for the Overtime Work under the following conditions :
For the first 1/2 hour – 100% of hourly wages
Next 1 hour – 170% of hourly wages
Next 1 hour – 170% of hourly wages
Rest hours – 200% of hourly wages
Let,
Total hours of Overtime works – 5
Hourly wages – $80/-
Please show me the calculation and formula.



debenhams page

My Home Page

Retrieving A Stock Price In Excel | Experiments in Finance