By J. Carlton Collins, CPA
Q. What happened to Excel's ability to import stock prices? That functionality seems to have vanished.
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.
Shop at Best Buy for Microsoft 365 & Office Software, Find Microsoft 365 and Office, Office 2010 and more in Home, Student, or Professional versions. Microsoft Corporation. For 1 PC or Mac. Turn data into useful insights. Compatible with Windows 10 or macOS. All languages included.
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.
-->Returns the price per $100 face value of a security that pays periodic interest.
Syntax
expression.Price (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7)
expression A variable that represents a WorksheetFunction object.
Parameters
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| Arg1 | Required | Variant | Settlement - the security's settlement date. The security settlement date is the date after the issue date when the security is traded to the buyer. |
| Arg2 | Required | Variant | Maturity - the security's maturity date. The maturity date is the date when the security expires. |
| Arg3 | Required | Variant | Rate - the security's annual coupon rate. |
| Arg4 | Required | Variant | Yld - the security's annual yield. |
| Arg5 | Required | Variant | Redemption - the security's redemption value per $100 face value. |
| Arg6 | Required | Variant | Frequency - the number of coupon payments per year. For annual payments, frequency = 1; for semiannual, frequency = 2; for quarterly, frequency = 4. |
| Arg7 | Optional | Variant | Basis - the type of day count basis to use. |
Return value
Double
Remarks
Important
Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text.
The following table describes the values that can be used for Arg7.
| Basis | Day count basis |
|---|---|
| 0 or omitted | US (NASD) 30/360 |
| 1 | Actual/actual |
| 2 | Actual/360 |
| 3 | Actual/365 |
| 4 | European 30/360 |
Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default.
Note
Visual Basic for Applications (VBA) calculates serial dates differently than Excel. In VBA, serial number 1 is December 31, 1899, rather than January 1, 1900.
The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, suppose a 30-year bond is issued on January 1, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.
Settlement, maturity, frequency, and basis are truncated to integers.
If settlement or maturity is not a valid date, Price returns the #VALUE! error value.
If yld < 0 or if rate < 0, Price returns the #NUM! error value.
If redemption ≤ 0, Price returns the #NUM! error value.
If frequency is any number other than 1, 2, or 4, Price returns the #NUM! error value.
If basis < 0 or if basis > 4, Price returns the #NUM! error value.
Microsoft Excel Price
If settlement ≥ maturity, Price returns the #NUM! error value.
Price is calculated as follows:
Microsoft Excel Price List
where:
- DSC = number of days from settlement to next coupon date.
- E = number of days in coupon period in which the settlement date falls.
- N = number of coupons payable between settlement date and redemption date.
- A = number of days from beginning of coupon period to settlement date.
Support and feedback
Microsoft Excel Price Quote Template
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.