Free Stock Price Quotes & Price Information in Microsoft Excel

Microsoft recently re-added the ability to add stock quote information to Excel spreadsheets. The date is delayed by 15 minutes, but you can click the update button to automatically have data for hundreds of stocks update.

They offer many fields in their data including:

  • ticker symbol
  • price
  • open
  • previous close
  • change %
  • change $
  • day’s high
  • day’s low
  • 52 week high
  • 52 week low
  • ticker symbol
  • beta (for most stocks, though on some this is blank)
  • today’s volume
  • average daily volume
  • stock float
  • market cap
  • industry
  • exchange
  • year founded
  • company description
  • CEO
  • employee count
  • headquarters

You can see the sort of spreadsheet you can create mostly automatically (though with a few manual tweaks) from the following image.

On the initial roll out of the feature they made it available for people who have active Office 365 subscriptions & who enabled the Office Insiders feature.

  • If you have an older version of Office or bought a key card the first step is to buy an Office 365 subscription.
  • The next step is to update to the latest version of Office, which may require restarting the computer. You can see if there are updates available by clicking on the “Account” link in the left rail menu of Excel. Then click on “Office Updates” to see if there are any updates available by clicking on the “Update Now” submenu that appears after clicking on the “Office Updates” button.
  • After that, open up Excel and once again click on the “Account” link in the left menu. Now in the right rail of the page you can click on the “Manage Account” button. That should either open up a pop up which lets you select Office 365 connection, or send you to a page on Microsoft’s site to enable Office 365. Here is a screenshot to show the areas referred to in this text. When you are done you should see Microsoft Office 365 listed in the right column.

Now the only step left is to enable Office Insider. Here are instructions directly from Microsoft on how to do that.

  1. In any Office 2016 app, click File > Account > Office Insider. Select the Get early access to new releases of Office box, specify an update level, and then click OK.
  2. Visit the Additional Install Options page of My Account. Sign in with your Microsoft Account, if needed. In the Version menu, click the type of Insider build you want to install, and then click Install. If you already have Office 2016 installed on your Windows desktop, you do not need to uninstall it first. You’ll be updated to the Office Insider build. If you don’t see an Insider option on the Version menu, you may not have an active Office 365 subscription.

Once you have done the above, make sure your Office Insider level is selected to Insider

After you save that, you can once again click on Update Options and click on Update Now from that menu.

You may have to close out of all Office documents & restart your computer again to get it to work, but you will know it works when you see the stock data type in the header area.

From there, you follow the instructions  given here to add stock data to a spreadsheet.

  • Type ticker symbols or company names into cells.
  • Create a table ( Insert > Table)
  • Select the cells with ticker symbols or company names in them
  •  Click on the top data tab in Excel & click on Stock data type. This should automatically link the stocks to the company name. Then you can click on the top right row in the table to add fields like current price, change, etc.
  • To update stock pricing data click on the “Refresh All”

If you have multiple computers and are struggling to update the second computer to the insiders setting you may have to visit this page.

For your convenience, here are a few sample stock spreadsheets, though they will only work if you have an Office 365 account, are an Office Insider & completed the above steps.

These spreadsheets use sortable columns which make it easy to quickly see

  • what has went up or down the highest percent
  • what had the biggest move today relative to its beta
  • what has gained or lost the most market cap today
  • how far a stock would need to fall to touch its 52-week lows
  • how much a stock would need to go up to touch its 52-week highs
  • the range of the stock today
  • the range of the stock this year
  • the range of the stock today vs the range of the stock this year
  • where stocks are in their 52-week channel & in its range for the day

A couple notes on the above spreadsheets.

  • Where the automated data connections worked we used whatever data Microsoft includes.
  • If automated data connections did not work for a particular data point, then we might have manually entered in the beta level for the stock price at that point in time.
  • Most the data is quite reliable, but in some cases the 52-week high or low data point seems to be stuck on a couple stocks like Ctrip. As Microsoft improves the offering & rolls it out more widely these issues will likely be fixed.
  • The weighting aspects on the indexes were based on data from SlickCharts from about a week ago.

The official Microsoft stock data integration is easy to update on an automated fashion quickly. If you prefer to have real-time stock data & have the data update without having to click on a refresh button then Michael Saunders offers a nice third-party plug-in. His plug-in is free & it offers real-time data for a one-time Paypal donation of only $10. He offers a downloadable spreadsheet for automated portfolio monitoring. His plug-in is great for watching a couple stocks or a small portfolio, but is less idea for looking at a broad index like the S&P 500 as you have to manually connect each stock one at a time.

 

 

Long Zillow (Z)

Zillow plunged hard on a revenue miss & announcing they were acquiring a loan origination shop named Mortgage Lenders of America for an undisclosed sum.

It is the second quarter in a row where Zillow bombed out after announcing soft earnings & jumping into a new business line.

However, in between the two earning reports, Zillow reached an all time high of $64.42. As of typing this Zillow is off $10.45 a share today, down 17.71% to $48.55, which puts them 24.6% below all time highs.

As much as some investors hate the Zillow Offers model which is essentially home flipping, Zillow expanding in that direction is probably a good call just to limit some of the potential investments in competitors who could start with that business model, build a destination site & then move across to compete against the core Zillow or Trulia sites.

With Softbank’s Vision Fund throwing hundreds of millions or even billions of Dollars around, preventing smaller startups from getting a solid footing with even a tiny vaporware side business is a big win.

And while owning a loan origination shop will have Zillow competing directly against other players on their platform, it shouldn’t be a significant risk as there are not many sites who have access to the core audience in the buying mode & most of the smaller independent players are way behind in share, brand awareness & technology.

If you are not one of the big 4 or 5 US banks (JP Morgan Chase, Bank of America, Wells Fargo, Citibank, US Bank) then you need to acquire customers through some other channel with a connection to the consumer. There’s only a few players with a strong footprint: Google, Zillow, Realtor.com, Bankrate. From there it is likely either a big step down in volume with something like Bing or a big step down in quality with sort of mystery meat clicks & leads from a lot of other providers.

If you look across the web a lot of the vertical destination sites have been bought out. Pure play sites like WebMD & Blue Nile have been going private for years. IAC & their former companies have rolled up players in categories like travel, dating, & home improvement.

One of my favorite tools for tracking how strong businesses are online is SEMrush. Here is a screenshot for some of their Zillow data.

This provides a quick “at-a-glance” view of their historical Google performance along with where they sort of fit in with their competitive set.

From that graphic you can see:

  • they keep getting stronger year over year
  • most of their ranking positions are in the top 20
  • their closest competitor is Realtor.com, which is on par with Trulia in terms of search engine exposure & estimated traffic

There are lots of estimates baked into data from such tools & maybe dominating the category means they won’t have a lot of room to grow via search directly, but if they are a strong destination site many people will bypass general search engines and visit the sites directly.

The SEMrush traffic analysis section estimates Zillow gets about half their desktop traffic directly from end users.

Zillow’s current market cap is a bit under $9 billion.

Silver Lake acquired Zoopla for £2.2 billion (equivalent to $3 billion at the prevailing exchange rate at the time the offer was made). Zoopla is one of the leading UK property platforms along with Rightmove.

Zoopla’s search traffic profile for their core site looks similar to the chart for Zillow.

Zoopla’s parent company which was acquired by Silver Lake also owns other properties like Uswitch, Money.co.uk, PrimeLocation & SmartNewHomes.

A few big differences between Zoopla & Zillow are:

  • Realtor.com is a distant second to Zillow in the US & is roughly tied with Trulia for search traffic.
  • Zoopla has a strong direct competitor which is publicly traded in RightMove, which is valued at around £4.48 billion or $5.8 billion.
  • The United States has a much larger population (& thus real estate market) than the United Kingdom does. The Census population clock puts the US population at 328.3 million. The same tool puts the UK population at 65.1 million.
  • The UK still has the looming Brexit related political risks.
    • If there is a hard Brexit that could in turn lower immigration, which would lower demand for property & thus property price appreciation rates.
    • Economist Steve Keen has mentioned how the UK having their own currency while being inside the EU has sort of made them act as a relief valve for some of the population movements as some of the southern European periphery countries have had migrations due to structurally weak economies from being tied into the same Euro currency that export powerhouse Germany uses.
    • The UK has also started pushing laws to go after ill gotten wealth from foreign oligarchs who parked their money in the London real estate market.
    • Between the two of those it could impact both the high and low ends of the UK real estate market.
    • Meanwhile the US real estate market is still quite strong, as it is heavily subsidized by long-term fixed rate loans which do not immediately increase ownership costs the way floating rate loans do & the U.S. Federal Government owning Fannie Mae & Freddie Mac.

Disclosure: the author was not paid to write this article & has established a position in Zillow stock today.