Microsoft 365 Family: Get premium versions of Word, Excel, PowerPoint, OneNote, and Outlook, 1TB OneDrive cloud storage and more for up to 6 people Excel 2016 - get it now with Office 365 With Office 365, you get all the latest Office apps plus 1TB OneDrive storage. A one-time purchase of Office 2016 - for either Windows PCs or Macs - costs $149 for the Office Home & Student; $229 for the Office Home and Business; and $399 for Office Professional.

• Buy Excel 2016
• Excel 2016 Stock Price Function
• Excel 2016 Price List

Q. What happened to Excel's ability to import stock prices? That functionality seems to have vanished.

Excel 2016 and Windows 10 14. The Office 2016 Ribbon 16. Exploring Excel 2016 18. Excel Online App 20. 2 Begin with Excel 21. The Excel Window 22. Create a Workbook 24. Add Data to the Worksheet 25. Build the Worksheet 26. Complete the Worksheet 29. Format the Text 30. Format the Numbers 31. Print the Worksheet 32.

A. Many CPAs treasured the ability to link real-time stock prices in Excel, but Microsoft removed this functionality a couple of years ago, reportedly because it was not fully compatible with Office 365. I'm happy to report that Microsoft has now brought back real-time stock prices, but they are available only in Office 365, and the steps for creating such a link are different, as illustrated in the following simple example.

I entered the ticker symbol DAL (for Delta Air Lines Inc.) in cell A3, and with cell A3 selected, from the Data tab I selected Stocks from the Data Types group, and then Delta Air LinesInc from the resulting Data Selector dialog box, as pictured below.

These actions create a live link between your workbook and the Nasdaq exchange database, which includes information from all the major U.S. stock indexes. Next, in cell B3, I began writing the formula =A3, which pops up a list of available stock data field names that I can insert in my workbook, a partial list of which is pictured below. Finally, I simply selected a field name (Price in this example) and pressed Enter to link the current Delta Air Lines stock price ($58.54 in this example) to my workbook. You can see the resulting formula in cell B3, which returns the current stock price for Delta, is =A3.price.

This functionality works even better when an Excel Table is involved, as follows. With my cursor positioned in cell A1, I inserted a table from the Inserttab by selecting Tableand clicking OK to create a table in cellsA1:A2. Next, I typed the two column heading names Company and Price in cells A1 and B1, respectively (in this example, my table area expanded automatically to include column B as I entered the column B name). Next, I increased the column width of column A by about 200%, and in cell A2, I entered MSFT (the ticker symbol for Microsoft) and then clicked the Stocks icon on the Data tab, as pictured below. This action launched the Data Selector dialog box pictured at the bottom right of the screenshot at the bottom of the previous page, and I selected the Microsoft Corp Stock - Nasdaq Stock option, which automatically linked the MSFT ticker symbol in cell A2 to the Nasdaq stock price database.

You can see in the screenshot below that Excel indicates this linkage with an icon of a columned building in cell A2, and Excel also automatically changes the MSFT ticker symbol to the more detailed name Microsoft Corp (XNAS:MSFT). Next I entered the formula =[Company].Price in cell B2 to return the current stock price for Microsoft ($125.83 in this example as of June 5, 2019).

The Table expands dynamically to incorporate additional Nasdaq data fields so you can prepare and manage a rather sophisticated portfolio. You can try this functionality yourself by downloading my example portfolio template, pictured below, at CarltonCollins.com/stock.xlsx. This template is set up to accommodate 100 investments — simply enter your stock ticker, category, and number of shares into the yellow cells, and Excel will instantly produce the corresponding data in the light blue cells.

About the author

J. Carlton Collins, CPA, (carlton@asaresearch.com) is a technology consultant, a conference presenter, and a JofA contributing editor.

Submit a question

Do you have technology questions for this column? Or, after reading an answer, do you have a better solution? Send them to jofatech@aicpa.org. We regret being unable to individually answer all submitted questions.

MarketXLS allows you to easily get historical stock prices for multiple time periods.

The options for getting historical pricing are available in the main marketXLS panel as shown below:

Buy Excel 2016

To use these options, simply select up to 10 cells with valid stock symbols and click on these buttons.

• Daily Minute Data: This will get the minute by minute pricing on today’s date
• 1 Year Data: This will get daily end of the day close prices of selected stocks for one year period
• Custom Date: This allows you to select the dates from when you want the data. This data is EOD daily. The data is available for all US stocks for 20 Years. And is updated daily in our database.

Excel 2016 Stock Price Function

Key features of the historical data are as follows:

• Covers all stocks trading on NASDAQ, AMEX, NYSE and ARCA.
• Includes unadjusted and adjusted open, high, low, close, volume.
• Includes dividend history and split history.
• Updated at or before 5pm on all trading days

The historical data will be nicely formatted and indexed on date with each column representing a symbol for easier comparison and further analysis.

Historical data also includes Dividend and Splits.

Excel 2016 Price List

More about Custom Dates

When you use custom dates to retreive historical stock prices, you have many options for the periodicity for which you want to get the data. You can choose to retrieve daily, weekly, monthly or quarterly stock prices.

Let’s say you want to retrieve stock prices for Google. Enter the stock symbol in a cell in the spreadsheet (GOOG). Once you’ve entered the stock symbol, click the ‘Custom Dates’ button in the menu. You will be presented with a custom date selection popup window. In this window, select the date range for which you want to get the stock prices. Along with that you can also change the periodicity to Weekly, Monthly, or Quarterly (default is daily). Now press the ‘Get Data’ button and the stock prices will be presented in a new sheet. I selected the time period of 1st January, 2016 to 31st October, 2016 and weekly periodicity. The results are shown below:

As you can see, you ow have the weekly historical stock prices for the Google stock. You can even select multiple stock symbols and the resulting table will contain all the inform for all the selected stocks (maximum 10). To present to you the weekly, monthly and quarterly data, we resample the daily data to the new periodicity.