How Can We Help?
How to Use Quotemedia’s Addon Data Functions
The new Quotemedia’s addon for MarketXLS allows you to get real-time streaming data for 300 symbols and a lot more industry grade data.
About the Data Addons
Depending on your needs, you can purchase the data addons relevent to you. For example, if you’e in the US, you can purchase the data add-ons for US real-time equities data, mutual funds EOD data, as well as real-time options data. Similarly there are various data addons available for both delayed and real-time quotes for different markets and asset classes such as forex, equities, options, commodities and more. You can view the complete list of add-on packages on our pricing page.
How to Use the Data Functions
Once you have a valid license of MarketXLS, and have purchased a Quotemedia’s data addon, you can update your addon subscription details in your MarketXLS settings, i.e., your username and password in the settings form (Settings/Help >> Settings>> Data Subscriptions >> Quotemedia). This is described in more detail here. Once this is done, you are now ready to start fetching the data in your Excel sheet by using simple functions.
There are two main categories of QM functions that allow you to get the data on the Quotemedia’s data on the fly. The first category is called “Quotemedia Refresh on Demand” and the second one is called “Quotemedia – Stream Real Time”.
All functions for using Quotemedia’s services start with a prefix “QM_”. When you start typing in Excel “=qm_”, Excel’s built in intelligence will show up functions as shown
below.
There are three main functions for Quotemedia. These functions are defined below:
Category : Quotemedia Refresh On Demand Functions
** The values given from the functions in this category are refreshed when you click on Refresh All or Refresh Selected buttons in the MarketXLS Menu. The values will not change themselves**
There are 2 main functions in this category as described below…..
- QM_Value()
Function: =QM_Value(Metric, Symbol)
Use this function if you want to get a single value in a cell. This functions gets you one value, and the value can be any stock information such as Last, Ask, Bid, EPS, PERATIO, Week 52 high date, Frequency of Dividend Distribution, Pay Date, Expiry date of an option, open interest, contracthigh, contract low.
A few examples:
- =QM_Value(“Last”, “MSFT”) – Gets the last stock price of Microsoft
- = QM_Value(“paydate”,”MSFT”) – Gets the pay date of the stock’s dividend
- =QM_Value(“eps”,”MSFT”) – gets the EPS of the stock
The format of this function is =QM_Value(“What you want to get”, “For which Symbol”).
The full list of all metrics available i.e. What you want to get, with this QM_Value function is mentioned below….
symbol
exchange
exLgName
exShName
isopen
iscurrentlyopen
opentime
closetime
timezone
marketplace
longname
shortname
last
change
changepercent
open
high
low
prevclose
bid
ask
bidsize
asksize
rawbidsize
rawasksize
tradevolume
sharevolume
totalvalue
vwap
lasttradedatetime
sharesoutstanding
marketcap
eps
peratio
pbratio
week52high
week52low
date
amount
yield
paydate
sharesescrow
callput
expirydate
strike
openinterest
contracthigh
contractlow
2. QM_List()
Function: =QM_List(“Method”, “Parameter1”, “Parameter Value 1”, “Parameter2”, “Parameter Value 2″,……,”Parameter N”, “Parameter Value N”)
Quotemedia’s API provides you access to over 80 methods to get different types of data. For example, a method called ‘getHistory’ allows you to get historical data for any stock for a given period. Other examples include GetIntraday Price History, Get Option Chains, Get Enhanced Fund Data ( fundgroup ,legalstatus, trusttype, annual return NAV, skewness, beta, sharperatio, stockholdings, weighting etc ).
The QM_List functions allows you to get the data from the Quotemedia’s API for any of these methods. The functions generally returns the data in a tabular (list-like) form. For example
For example, to to get intraday history for a symbol use the function as shown below:
=QM_List(“getIntervals”, “symbol”,”MSFT”, “interval”,”5″).
So the structure of this function is as follows:
=QM_List(“What data set you need – the name if the method”, “Parameter1”, “Parameter Value 1”, “Parameter2”, “Parameter Value 2″,……,”Parameter N”, “Parameter Value N”)
There is a separate detailed article here that explains all the methods with the link to their description here.
3. Other Refresh on Demand functions…
Even though the =QM_Value function as explained in point 1 is sufficient to get the data points, we have created some more commonly used functions in a way that you do not have to enter the parameter names.
For example…. =qm_value(“last”,”MSFT”) is same as =qm_last(“MSFT”). We have just provided some quick overlap for some commonly used datapoints in a way that they take only one argument (which is the symbol).
Category : Quotemedia Stream Real-Time Functions
**This category of functions result in values changing by themselves without you having to Refresh the data. A connection is made in the background to the servers and whenever the new data points arrives its shown in the cell**
The prefix for functions in this category is =qm_stream_
The main function in this category is =qm_stream_value(“What do you want to stream”, “For which symbol you want to stream”). For example, to stream the last price of a stock type =qm_stream_value(“last”,”symbol”)
Just like in =qm_value function we have pre-packaged if you like some of the commonly streamed values as extra functions that take only one argument which is the symbol.
For example… =qm_stream_value(“last”,”symbol”) is same as =qm_stream_last(“symbol”)
A separate articles on streaming real time data with Quotemedia’s addon with MarketXLS is available here.
