Alternative to Excel Pulling Stock Quotes from MSN Money - Use Google Sheets

% of readers think this story is Fact. Add your two cents.

The Microsoft Excel stock quotes plug-in stopped working the way it was designed to. Maybe it is temporary or maybe it is here to stay. Regardless, here is a temporary or with a little effort you could make it a permanent solution.

Start using Google Spreadsheets. My only gripe about it years ago was that you were not able to take it offline or had to go through a number of hoops to take it offline. However, they’ve fixed it since and now you can save it to a Google Drive and access it offline. 

When you open Google Sheets, you can create a new sheet by clicking on the huge green plus sign on the bottom right corner. Next it will take you to a sheet that looks very much like Microsoft Excel. Like with Excel formulas, you can actually put in a stock formula that will pull delayed quotes from Google Finance. 

The formula is =GOOGLEFINANCE(ticker). This will default to the price of the stock. Similar to Excel, you are also able to select a cell with a ticker versus typing in the ticker. Your formula would look like =GOOGLEFINANCE(A1). Whereby A1 is the cell in which you have your ticker symbol. 

What is so great about Google Sheets is that you can do a lot more than you could in Excel. For example, if you are looking for the current day volume, just simply put “volume” after the ticker as the “[attribute]“. It would look like this =GOOGLEFINANCE(NKE, “volume”). 

Another useful function of Google Sheets is that you can pull historical stock price data straight from Google’s stock database. In the below example, when you specify the symbol, attribute, start_date, num_days or end_date, and 1 interval, Google will automatically pull that information into your sheet. 

You might decide to forgo Microsoft Excel stock plug-in and use Google Sheets. Google Sheets lets you import your old Excel sheets. That way you don’t need to start from scratch. Just go to File -> Import and then select your Excel sheet.

What else can Google Sheets do? It can also pull quote and data for mutual funds and foreign exchange trends. 

A number of useful attributes for pulling stock information from Google Finance are all shown below. 

Syntax

• ticker - The ticker symbol for the security to consider.
• attribute - [ OPTIONAL - "price" by default ] - The attribute to fetch about ticker from Google Finance.
  • attribute is one of the following for realtime data:
    • "price" - Realtime price quote, delayed by up to 20 minutes.
    • "priceopen" - The price as of market open.
    • "high" - The current day’s high price.
    • "low" - The current day’s low price.
    • "volume" - The current day’s trading volume.
    • "marketcap" - The market capitalization of the stock.
    • "tradetime" - The time of the last trade.
    • "datadelay" - How far delayed the realtime data is.
    • "volumeavg" - The average daily trading volume.
    • "pe" - The price/earnings ratio.
    • "eps" - The earnings per share.
    • "high52" - The 52-week high price.
    • "low52" - The 52-week low price.
    • "change" - The price change since the previous trading day’s close.
    • "beta" - The beta value.
    • "changepct" - The percentage change in price since the previous trading day’s close.
    • "closeyest" - The previous day’s closing price.
    • "shares" - The number of outstanding shares.
    • "currency" - The currency in which the security is priced.
  • attribute is one of the following for historical data:
    • "open" - The opening price for the specified date(s).
    • "close" - The closing price for the specified date(s).
    • "high" - The high price for the specified date(s).
    • "low" - The low price for the specified date(s).
    • "volume" - The volume for the specified date(s).
    • "all" - All of the above.
  • attribute is one of the following for mutual fund data:
    • "closeyest" - The previous day’s closing price.
    • "date" - The date at which the net asset value was reported.
    • "returnytd" - The year-to-date return.
    • "netassets" - The net assets.
    • "change" - The change in the most recently reported net asset value and the one immediately prior.
    • "changepct" - The percentage change int he net asset value.
    • "yieldpct" - The distribution yield, the sum of the prior 12 months’ income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month’s net asset value number.
    • "returnday" - One-day total return.
    • "return1" - One-week total return.
    • "return4" - Four-week total return.
    • "return13" - Thirteen-week total return.
    • "return52" - Fifty-two-week (annual) total return.
    • "return156" - 156-week (3-year) total return.
    • "return260" - 260-week (5-year) total return.
    • "incomedividend" - The amount of the most recent cash distribution.
    • "incomedividenddate" - The date of the most recent cash distribution.
    • "capitalgain" - The amount of the most recent capital gain distribution.
    • "morningstarrating" - The Morningstar “star” rating.
    • "expenseratio" - The fund’s expense ratio.
• start_date - [ OPTIONAL ] - The start date when fetching historical data.
  • If start_date is specified but end_date|num_days is not, only the single day’s data is returned.
• end_date|num_days - [ OPTIONAL ] - The end date when fetching historical data, or the number of days from start_date for which to return data.
• interval - [ OPTIONAL ] - The frequency of returned data; either “DAILY” or “WEEKLY”.
  • interval can alternatively be specified as 1 or 7. Other numeric values are disallowed.

Notes

• All parameters must be enclosed in quotation marks or be references to cells containing text, except when interval is specified as a number and when end_date|num_days is specified as a number of days.
• Realtime results will be returned as a value within a single cell. Historical data, even for a single day, will be returned as an expanded array with column headers.
• GOOGLEFINANCE is only available in English and does not support most international exchanges.