Rajandran R Founder of Marketcalls and Co-Founder Algomojo. Full-Time Derivative Trader. Expert in Designing Trading Systems (Amibroker, Ninjatrader, Metatrader, Python, Pinescript). Trading the markets since 2006. Mentoring Traders on Trading System Designing, Market Profile, Orderflow and Trade Automation.

How to Get Futures and Options Data and Commodities Data into Google Spreadsheet

4 min read

We know that google spreadsheets support an inbuilt function google finance to fetch real-time stock quotes for Equities markets. However, do you know how to get Futures and Options Data, Commodities Data into Google Spreadsheet?

In this tutorial, we are going to use Google Appscript to fetch real-time stock quotes using your Brokers API for NSE Cash, NSE Futures & Options, MCX, NSE Currency, BSE Currency Data into the Google Spreadsheet.

Requirements

1)Algomojo API
2)Google Spreadsheets
3)Trading Account with Algomojo Supported Brokers (Currently, Algomojo Supports Aliceblue, Tradejini, Upstox & Zebu)

What is Google AppScript?

Apps Script lets you do more with Google Spreadsheets and with other Google Products, all on a modern JavaScript platform in the cloud. Build solutions to boost your collaboration and productivity.

You can access the Appscript Editor from Tools->Script Editor from your Google Spreadsheet.

What is Algomojo?

Algomojo offers Free API + Free Trading Platform to algomojo users to Place, Modify, Cancel Orders. Currently, Algomojo API is free for the users who are opening a trading account with Algomojo Connected Brokers.
Free API platform + Free Algo Trading platform is offered with no upfront fees,  no minimum turnover, no special terms and conditions, no clauses, no strings attached.

Learn How to send Automated Orders using Google Spreadsheet

Writing the Google Appscript

Open the Script Editor and Paste the below appscript code and save the code as Algomojo

Get the API Key and API Secret Key, Broker Shortcode from Algomojo Platform and replace the apikey and apisecret key & Broker in the below code with yours.

Broker Short Code

//Broker Short Code
'ab' - Aliceblue, 'tj' - tradejini , 'up' - upstox, 'zb' - Zebu
function Algomojo(Symbol,Exchange,Field) {

ApiKey = "8dedb0aae13f84107fa22651e4c53284";
ApiSecret = "f9f6aed90706002663adfb45f632f6a3";
Broker = "up"; //Broker Shortcode   'ab' - Aliceblue, 'tj' - tradejini , 'up' - upstox, 'zb' - Zebu
var Version = "1.0";
var iField ="";
var ApiName = "";
var result = "";


if(Broker == "tj" || Broker == "zb" || Broker == "ab"){
  ApiName = "GetQuote";
  if(Field == "ltp") { iField = "LTP"; }
  if(Field == "open") { iField = "openPrice"; }
  if(Field == "high") { iField = "High"; }
  if(Field == "low") { iField = "Low"; }
  if(Field == "prevclose") { iField = "PrvClose"; }
  if(Field == "atp") { iField = "vwapAveragePrice"; }
  if(Field == "volume") { iField = "TradeVolume"; }
  if(Field == "change") { iField = "Change"; }
  if(Field == "perchange") { iField = "PerChange"; }

  if(Field == "oi") { 
    iField = "openinterest"; 
    var TokenNo = Fetchtoken(ApiKey, ApiSecret, Broker, Version, Symbol);
    var SecurityResp = SecurityInfo(ApiKey, ApiSecret, Broker, Version, Exchange, TokenNo);
    var obj = JSON.parse(SecurityResp);
    result = obj[iField];
    return result;
    }
  
 }

if(Broker == "up"){
  ApiName = "Feed";
  iField = Field;
  if(Field == "volume") { iField = "vtt"; }
  if(Field == "prevclose") { iField = "close"; }
  if(Exchange == "NSE") { Exchange = "nse_eq"; }
  if(Exchange == "NFO") { Exchange = "nse_fo"; }
  if(Exchange == "MCX") { Exchange = "mcx_fo"; }
  if(Exchange == "CDS") { Exchange = "ncd_fo"; }
  if(Exchange == "BSE") { Exchange = "bse_eq"; }
  if(Exchange == "BCD") { Exchange = "bcd_fo"; }

 }

 var response = GetQuote(ApiKey, ApiSecret, Broker, Version,Symbol,Exchange,iField,ApiName);

return response;
}

function GetQuote(ApiKey, ApiSecret, Broker, Version,Symbol,Exchange,iField,ApiName){

  var apidata = "";

  if(Broker == "tj" || Broker == "zb" || Broker == "ab"){  
  
  apidata = "{\""
      + "Exchange" + "\":\"" + Exchange + "\",\""
      + "Symbol" + "\":\"" + Symbol + "\"}";

  }

  if(Broker =="up"){
    apidata = "{\""
      + "exchange" + "\":\"" + Exchange + "\",\""
      + "symbol" + "\":\"" + Symbol + "\",\""
      + "type" + "\":\"" + "full" + "\"}";
  }



  var response = AMConnect(ApiKey, ApiSecret, Broker, Version, ApiName, apidata);

  var json = response;
  var data = JSON.parse(json);

  if(Broker == "up" && iField=="change")
  {
    var change = parseFloat(data["data"]["ltp"]) - parseFloat(data["data"]["close"]);
    result = change.toString();
    return result
  }

  if(Broker == "up" && iField=="perchange")
  {
    var perchange = (parseFloat(data["data"]["ltp"]) - parseFloat(data["data"]["close"]))*100/parseFloat(data["data"]["close"]);
    result = perchange.toString();
    return result
  }



  if(Broker == "tj" || Broker == "zb" || Broker == "ab"){  
    result = data[iField];
  }

  if(Broker == "up"){  
   result = data["data"][iField];
  }

    return result;
}

function Fetchtoken(ApiKey, ApiSecret, Broker, Version, Symbol){
  var apidata = "{\""
     + "s" + "\":\"" + Symbol + "\"}";

  var response = AMConnect(ApiKey, ApiSecret, Broker, Version, "fetchtoken", apidata);

  var json = response;
  var data = JSON.parse(json);
  return data["token"];
}

function SecurityInfo(ApiKey, ApiSecret, Broker, Version, Exchange, TokenNo){
  var apidata = "{\""
      + "Exchange" + "\":\"" + Exchange + "\",\""
      + "SrchFor" + "\":\"" + TokenNo + "\"}";

  var response = AMConnect(ApiKey, ApiSecret, Broker, Version, "SecurityInfo", apidata);

  return response;
}

function AMConnect(ApiKey, ApiSecret, Broker, Version, api_name, apidata){
    
    var BaseURL = "https://" + Broker.toLowerCase() + "api.algomojo.com/" + Version + "/";
    var postdata = "{\"" + "api_key" + "\":\"" + ApiKey + "\",\"" 
                    + "api_secret" + "\":\"" + ApiSecret + "\",\""
                    + "data" + "\":" + apidata + "}";
      
    var Url = BaseURL + api_name;
   
  
    var raw = postdata;
    
    Logger.log(raw);
    
  
    var options = {
      'method' : 'post',
      'contentType': 'application/json',
      // Convert the JavaScript object to a JSON string.
      'payload' : raw
    };
    var result = UrlFetchApp.fetch(Url, options);
    return result.getContentText();
    
}

How to Access the Algomojo User Defined Function

Now after setting the Apikey, Apusecret & Broker short code one have to start accessing the quotes using algomojo user defined function. Here is the formula to access the quotes

=Algomojo("Symbol", "Exchange", "Field");

Symbol Formatting

Where symbol is the ticker name as the broker symbol format. If not sure about the symbol. Log into the Algomojo trading platform and checkout the symbol format. Symbol format varies and different broker adapts different formatting when comes to the Symbol Format.

For example the symbol for Reliance in Tradejini, Aliceblue, Zebu is “Reliance-EQ” and for upstox brokers the symbol format is only “RELIANCE”

Supported Exchanges Parameter

ExchangeDescription
NSENSE Equities
BSEBSE Equities
NFONSE Futures & Options
NCDNSE Currencies
BCDBSE Currencies
MCXMCX Futures & Options
NSE_IDXNSE Index
BSE_IDXBSE Index
Exchange Parameter supported by Algomojo

Field Parameter

Field parameter defines what information about the stock quotes one want to extract. Following are the supported field parameters

FieldDescription
ltpLast Traded Price
openTodays Open
HighTodays High
LowLow
prevclosePrev Close
volumeTodays Total Volume
oiOpen Interest
changeTodays Price Change
perchangeToday Price Percentage Change
Fields Parameter supported by Algomojo

Calling the Algomojo Functions from Google Spreadsheets to access Last Traded Price

=Algomojo("NIFTY21FEBFUT","NFO","ltp")

Calling the Algomojo Functions from Google Spreadsheets to access Options Open Interest Data

=Algomojo("NIFTY2121115000CE","NFO","oi")

Calling the Algomojo Functions from Google Spreadsheets to access Commodities Last Traded Price

With these information you should be able to get any traded quote from Indian Markets into Google Spreadsheet in Realtime. And to update the Algomojo Functions frequently one can consider adding triggers.

How to Add Triggers

1)From the Google Spreadsheet goto Tools -> Script Editor ensure that Algomojo.gs appscript is open and the Apikey, Apisecret key and broker shortcode is set properly

2)One the Left hand side select the Triggers option
3)Click on Add Trigger button

4)Now select the following values from the dialog box as shown below
i)select event source as Time-Driven
ii)Select type of time-based trigger as Minutes timer
iii)Select minute interval as Every minute or any other value from the list depends upon your frequency of data access and the minimum possible interval is 1-minute

Press save to save the values

Once the Trigger is saved you will be able to access the Triggers details as shown below

Note

Avoid Calling too many symbols. Limit yourself upto 25-50 algomojo functions as most of the brokers does rate limiting on API calls. Rate limiting defines limits on how many API calls can be made within a specified time period. Rate limits are imposed on every app.

If the Limit exceeds the API function might throw an error to the user. And many brokers also impose 1 request/second rate limit on accessing the stock quotes.

Access the Sample Google Spreadsheet here

Hope this solves your quote access requirement. If in case you need more details you can send your queryto support@algomojo.com

Rajandran R Founder of Marketcalls and Co-Founder Algomojo. Full-Time Derivative Trader. Expert in Designing Trading Systems (Amibroker, Ninjatrader, Metatrader, Python, Pinescript). Trading the markets since 2006. Mentoring Traders on Trading System Designing, Market Profile, Orderflow and Trade Automation.

How to Get Futures and Options Data and Commodities…

We know that google spreadsheets support an inbuilt function google finance to fetch real-time stock quotes for Equities markets. However, do you know how...
Rajandran R
4 min read

Merge Indices & Bhavcopy text files into One text…

Use the link given to access the Excel VBA that will merge different EOD Indices files to the main Bhavcopy file. This is a...
rajib
36 sec read

VBA Macro to Download Bhavcopy

Here is a initiative from me to automate the NSE EOD data in ASCII format using simple VBA Scipts in MS Excel. The following...
Rajandran R
1 min read

Leave a Reply

Get Notifications, Alerts on Market Updates, Trading Tools, Automation & More