This code works on Excel, Office for Mac 2011. It will put the quote to the right of the cell with the macro call. Function StockQuote(strTicker As String) Call StockQuote2(strTicker, ActiveCell.Offset(0, 1).Address) End Function. Function StockQuote2(strSymbol As String, strDest As String) Dim strURL As String. According to the Excel team agent who replied to it, the project is already started, when the function is released, they will post there too. Meanwhile, there is a post from Jim Gordon in this thread, he suggested an add-in in Excel for Mac, you may consider it as a workaround: Can no longer get stock quotes in Excel 2016 on Mac. Discover historical prices for MAC stock on Yahoo Finance. View daily, weekly or monthly format back to when Macerich Company (The) stock was issued.
Excel Quotes Add-In
Excel Quotes Add-In has been broken recently due to changes in Microsoft's Quote Web Services. Please see this post for more information.
On 6/30/2019, I have revere-engineered the new Quote Web Services and updated my Excel Quotes Add-In with the changes. Please download the most recent version and copy over the previously saved QuotesAddIn.xll file on your computer.
On 6/30/2019, I have revere-engineered the new Quote Web Services and updated my Excel Quotes Add-In with the changes. Please download the most recent version and copy over the previously saved QuotesAddIn.xll file on your computer.
A long time ago, my team at Microsoft built an Excel plug-in to get stock quotes from MSN Money. It looked something like this: MSNStockQuote('MSFT','Last'). The final station 1 0.
Unfortunately Microsoft hasn't supported this plug-in for years :(
While building MSMoneyQuotes, I reconginzed Excel was still lacking a decent Add-In to get quotes. So I built one!
The Excel Add-In I developed uses Microsoft's own quote web services to retrieve quotes. Therefore it supports getting quotes for any securities that work on the MSN Money website and/or the Windows 10 Money app. I also added the ability to get cryptocurrency quotes using the CryptoCompare API.
I have made it freely available from my website. However, I do accept donations which help offset the many hours put towards building, improving and supporting this software.
Unfortunately Microsoft hasn't supported this plug-in for years :(
While building MSMoneyQuotes, I reconginzed Excel was still lacking a decent Add-In to get quotes. So I built one!
The Excel Add-In I developed uses Microsoft's own quote web services to retrieve quotes. Therefore it supports getting quotes for any securities that work on the MSN Money website and/or the Windows 10 Money app. I also added the ability to get cryptocurrency quotes using the CryptoCompare API.
I have made it freely available from my website. However, I do accept donations which help offset the many hours put towards building, improving and supporting this software.
![Stock Stock](https://www.someka.net/wp-content/uploads/2019/03/Historical-Stock-Prices-Scraper-Someka-Excel-Template-S01-1.png)
Prerequisites
- Works with Desktop Excel 2010, 2013, 2016
- Supports 32 bit and 64 bit versions of Excel
- Built as an 'Excel Add-In', which is a special DLL with an XLL file extension
- Adds a new '=QUOTE()' function to make getting quotes easy
- Adds a new 'Quotes' tab to make finding and updating quotes easy
- Supports updating quotes for all securities supported by the MSN Money website
- Supports updating cryptocurrency quotes supported by the CryptoCompare API
Download and Install
- First determine which bit version of Excel you have installed using What version of Office am I using?
- Download the appropriate 32-bit version or 64-bit version of QuotesAddIn.xll and save on your computer (e.g. C:GaierSoftwareQuotesAddIn.xll)
- Add/Enable the Add-In in Excel using the 'Browse' option. Add or remove add-ins in Excel
- Click File/Options then click 'Go' next to Manage Excel Add-ins
- Click 'Browse..', navigate to the folder where you saved the QuotesAddIn.xll and select it
Dowload the 32 bit version - QuotesAddIn.xll
Dowload the 64 bit version - QuotesAddIn64.xll
Example Excel File
After you've downloaded and installed the Add-In, please see my sample Excel file to see how this works!
QuotesAddInExample.xlsx
QuotesAddInExample.xlsx
Basic Usage
The Excel Quote Add-In works by providing a new Excel function (aka formula) called =QUOTE(). It can be used to retrieve quote data like last, open, day high/low, year high/low, etc.
The function accepts two parameters:
The function accepts two parameters:
- Financial Instrument: This the special symbol MSN Money (and Morningstar) assigns to the security to uniquely identify it. Because MSN Money supports many international markets the symbol 'GOOG', for example, is not enough to uniquly identify Google. Instead, MSN Money uses the following Finanical Instrument to indentify Google: '126.1.GOOG.NAS'. Financial Instrument Search Page
- Quote Field: This indicates which quote field you'd like to retreive. See the 'Supported Quote Fields' for the complete list.
Example Usage:
How to determine the appropriate 'Financial Instrument' symbol
I've build it a basic search pane that makes finding a MSN Money supported financial instrument easy. Go to the custom 'Quotes' tab and click the 'Search' button.
The 'Quotes' tab has a 'Search', 'Update Now' and 'Start Auto Update' buttons:
In the search pane, you can search by symbol or company name and hopefully find what you're looking for. Just start typing in the search field and the listbox will automatically load relevant results. This uses the same search service used by the MSN Money website, so I can't control the search behavior and/or results.
Once you find what you're looking for, you have the option to:
The 'Quotes' tab has a 'Search', 'Update Now' and 'Start Auto Update' buttons:
In the search pane, you can search by symbol or company name and hopefully find what you're looking for. Just start typing in the search field and the listbox will automatically load relevant results. This uses the same search service used by the MSN Money website, so I can't control the search behavior and/or results.
Once you find what you're looking for, you have the option to:
- Select the Quote Field you'd like to get a value for and click 'Insert Forumla' to insert the applicable =QUOTE() forumla
- Select 'Financial Instrument' to insert the raw financial instrument into the currently active call. This does not insert a formula. This may be useful if you're using the second approach [e.g. =QUOTE(A1, 'Last')] where you're referencing another cell to get the financial instrument.
Cryptocurrency Support
Cryptocurrencies are a hot topic recently, so I also added support for those. I leverage the CryptoCompare API to get quotes.
If you know the cryptocurrnecy pair, you can simply type the formula directly into the cell. For example: Or you can use the 'Cryptocurrency' tab in the search pane. Same as the other tab, start typing in the search field and relevant results will show up.
If you know the cryptocurrnecy pair, you can simply type the formula directly into the cell. For example: Or you can use the 'Cryptocurrency' tab in the search pane. Same as the other tab, start typing in the search field and relevant results will show up.
Supported Quote Fields
Here's a list of Quote Fields and which security types they're generally applicable to. Not all fields work for every security.Quote Field | Indexes | Stocks | ETFs | Funds | Currency Rates | Cryptocurrencies |
---|---|---|---|---|---|---|
Symbol | X | X | X | X | X | X |
Name | X | X | X | X | ||
Currency | X | X | X | X | ||
Last | X | X | X | X | X | X |
Change | X | X | X | X | X | X |
Date | X | X | X | X | X | |
Type | X | X | ||||
Exchange | X | X | X | |||
Previous Close | X | X | X | |||
Day High | X | X | X | X | ||
Day Low | X | X | X | X | ||
Open | X | X | X | X | ||
PE | X | |||||
EPS | X | |||||
Volume | X | X | X | X | ||
Avg Volume | X | X | X | |||
Year High | X | X | X | X | ||
Year High | X | X | X | X | ||
Market Cap | X | X | ||||
Ask | X | X | X | |||
Beta | X | |||||
Bid | X | X | X | |||
Dividend Yield | X | X | ||||
Industry | X |
How to update quotes
What is the home button on mac. Once the cell(s) are setup with the proper =QUOTE() forumla, you can go to the custom 'Quotes' tab and click the 'Update Now' button. This button forces a 're-calculation' of all =QUOTE() forumulas, which invokes my add-in to retrieve quotes from the relevant web service APIs.
To automatically update quotes every 1 minute, click the 'Start Auto Update' button. Essentially this starts a timer to automatically run the 'Update Now' logic every minute.
Click 'Stop Auto Update' to stop the auto update timer.
Note: Quotes are cached for 30 seconds, so the if you try to click the 'Update Now' button more often than that nothing will happen.
To automatically update quotes every 1 minute, click the 'Start Auto Update' button. Essentially this starts a timer to automatically run the 'Update Now' logic every minute.
Click 'Stop Auto Update' to stop the auto update timer.
Note: Quotes are cached for 30 seconds, so the if you try to click the 'Update Now' button more often than that nothing will happen.
Help and Support
I've made this free to avoid creating a sense of obligation that I'll provide support. I don't guarentee it will work in every scenario or that I'll provide support when it doesn't work. With that said, if you do have a problem that you think I can help shoot me an email and I'll reply if I can.
Please recognized I am no longer a Microsoft employee and cannot fix quote issues on MSN Money. Nor can I address outages that the MSN Money quote web service may have. My Excel Quote Add-In is not responsible for the actual quote values returned by MSN Money, so please don't email asking me to fix such issues. Same with the cryptocurrency quotes from the CryptoCompare API.
I can be reached at my support email address: [email protected]
Please recognized I am no longer a Microsoft employee and cannot fix quote issues on MSN Money. Nor can I address outages that the MSN Money quote web service may have. My Excel Quote Add-In is not responsible for the actual quote values returned by MSN Money, so please don't email asking me to fix such issues. Same with the cryptocurrency quotes from the CryptoCompare API.
I can be reached at my support email address: [email protected]
Release History
- Version 1.0 - 01/31/2018 - Initial Release
- Version 1.1 - 02/22/2018 - Added Auto Quotes Update
- Version 1.2 - 06/30/2019 - Updated to consume the new Microsoft Quote Services
Add stock information to your spreadsheet
- Tap or click the cell you want to add stock information to.1
- On your iPhone, iPad, or iPod touch, tap Cell > Stock Quote. You might need to swipe up to see it.
- On your Mac, click the Insert button , then choose Stock Quote.
- On your iPhone, iPad, or iPod touch, tap Cell > Stock Quote. You might need to swipe up to see it.
- Choose a stock in the list. If you want to search for a specific stock, enter the company name or stock symbol.
- Choose the attribute you want to track.
- On your iPhone, iPad, or iPod touch, tap the attribute you want to track in this cell. The attribute you’re tracking has a to the left of it. Tap Done.
- On your Mac, in the Attribute pop-up menu, choose what information you want to track in this cell. Click outside the dialog.
If you want to see updated information as of the previous day's close or change the attribute you're tracking, double-tap or double-click on the cell.
You can also use the STOCK formula to enter and edit stock information in a cell. Here's how to edit the STOCK as a formula:
- On your iPhone, iPad, or iPod touch, tap on the cell, tap Cell, then tap Edit Formula.
- On your Mac, double-click on the cell, then click Edit as Formula.
1You must be connected to the Internet to add stock information. If Stock Quote is dimmed, the Internet might be unavailable. Check your network connection. When you're offline, any cell that actively pulls information from the Internet is empty.
Stock attributes that you can track
![Price Price](https://office-watch.com/fredagg/uploads/real-time-excel-get-live-stock-prices-currency-rates-and-more-23994.png)
When you edit the STOCK formula, you can use any of these strings or numbers to show different pieces of data:
- “price” (0 or omitted): The share price of the specified stock at the market close of the previous market day.
- “name” (1): The full name of the stock or company.
- “change” (2): The difference between the last trade on the previous market day and the closing price on the market day prior to that. If the stock hasn’t traded in that interval, the change reported is “0.”
- “percent change” (3): The percentage change in the stock’s two most recent closing prices.
- “open” (4): The starting price at which the stock traded at the opening of trading on the previous market day.
- “high” (5): The highest price at which the stock traded during the previous market day.
- “low” (6): The lowest price at which the stock traded during the previous market day.
- “market cap” (7): The total market value of all the outstanding shares of the stock on the previous market day. This is calculated as the total number of outstanding shares multiplied by the price per share.
- “volume” (8): The number of shares of the stock that changed hands during the previous market day.
- “yield” (9): The ratio of the stock’s annual dividend (cash payout) per share as a percentage of the share price.
- “1-year target” (10): The one-year target price estimate, which is the median target price as forecast by analysts covering the stock.
- “52-week high” (11): The stock’s highest trade price in the last 52 weeks.
- “52-week low” (12): The stock’s lowest trade price in the last 52 weeks.
- “avg. 3-month volume” (15): The monthly average of the cumulative trading volume during the last 3 months divided by 22 days.
- “beta” (16): The measure of the volatility (systematic risk) of a security or commodity in comparison to the market as a whole.
- “currency” (19): The currency in which the stock is priced.
- “annual dividend” (20): The yearly dividend (cash payout) amount per share.
- “eps” (21): Earnings per share—calculated as a company’s total earnings divided by the number of outstanding shares (the stock currently held by all its shareholders).
- “exchange” (22): The stock exchange on which the stock is traded (for example, NYSE, NASDAQ, Euronext, and so on).
- “p/e ratio” (23): The price/earnings ratio, calculated by dividing the stock’s current market price by the trailing 12-month earnings per share.
- “previous close” (24): The stock’s closing price for the trading day prior to the last trade reported.
- “symbol” (25): The stock symbol (ticker symbol) that uniquely identifies the stock.
Track stock history
If you want to track the history of a specific stock, use STOCKH. When you add the formula, you must define these attributes:
- Symbol: An abbreviation that uniquely identifies publicly traded shares of a stock on a particular stock market, enclosed in quotes, or a reference to a cell that contains the symbol.
- Attribute: An optional value specifying the stock attribute to be returned. Numbers automatically suggests 'close.' Instead of close, you can choose open, high, low, or volume.
- Date: The date for which you want the historical stock price information
Add currency exchange rates to your spreadsheet
You can use the CURRENCY formula to pull data about currency exchange rates from the Internet and use them in your spreadsheet. When you add the formula, you must define these attributes:
- Currency-1: This is the currency code for the currency from which you’re converting. Use quotes around your string.
- Currency-2: This is the currency code for the currency to which you’re converting. Use quotes around your string.
For your third attribute, Numbers automatically suggests “price.' If you enter 0 or omit this item entirely, the cell shows the exchange rate of currency-1 to currency-2, expressed as currency-2.
Currency attributes that you can track
When you edit the formula, you can use any of these strings or numbers to show different pieces of data:
- “name” (1): The currency codes of the specified currencies, shown in the formula for calculating the exchange rate.
- “change” (2): The difference in the exchange rate at the close of trading on the two most recent business days.
- “percent change” (3): The percentage change in the exchange rate’s two most recent closing prices.
- “open” (4): The exchange rate at the opening of trading on the previous business day.
- “high” (5): The highest exchange rate on the previous business day.
- “low” (6): The lowest exchange rate on the previous business day.
- “52-week high” (7): The highest exchange rate in the last 52 weeks.
- “52-week low” (8): The lowest exchange rate in the last 52 weeks.
Get Stock Price Excel For Mac 2011 Price
Track currency history
If you want to track the history of a specific currency, use CURRENCYH. When you add the formula, you must define these attributes:
Get Stock Price Excel For Mac 2011 Free
- Currency-1: The currency code for the currency from which you’re converting. currency‑1 is a string enclosed in quotes.
- Currency-2: The currency code for the currency to which you’re converting. currency‑2 is a string enclosed in quotes.
- Attribute: An optional value specifying the currency attribute to be returned. Numbers automatically suggests 'close.' Instead of close, you can choose open, high, low, or any other currency attribute (see Currency attributes that you can track).
- Date: The date for which you want the historical exchange rate information.