How to Use Quotemedia’s Data Functions

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). 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 Quotemedia’s data on the fly.

• Refresh on Demand
• Stream Real-Time

All functions for using Quotemedia’s services start with the prefix “=QM_”. When you start typing in Excel “=qm_”, Excel’s built-in intelligence will show up functions as shown 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…..

1. QM_Value()

   Function:

   =QM_Value(Metric, Symbol)

   Use this function if you want to get a single value in a cell. This function gets you one value, and the value can be any stock information such as Last, Ask, Bid, EPS, PE RATIO, Week 52 high date, Frequency of Dividend Distribution, Pay Date, Expiry date of an option, open interest, contract high, contract low.

   A few examples:

   1. =QM_Value("Last", "MSFT")
      – Gets the last stock price of Microsoft
   2. =QM_Value("paydate", "MSFT")
      – Gets the pay date of the stock’s dividend
   3. =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
   • lastchange
   • 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 function allows you to get the data from Quotemedia’s API for any of these methods. The functions generally return the data in a tabular (list-like) form.

   For example, 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 of the method”, “Parameter1”, “Parameter Value 1”, “Parameter2”, “Parameter Value 2″,……, "Parameter N”, “Parameter Value N”)

   LINK TO QM_LIST PAGE

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 data points in a way that they take only one argument (which is the symbol).