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

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 – Nest Terminal Users

//Broker Short Code - Nest Terminal Users
'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();
    
}

Broker Short Code

//Broker Short Code - Kambala Terminal Users
'tc' - Tradejini Cube, 'fs' - Firstock

function Algomojo(Symbol,Exchange,Field) {

Uid = "TM00001"; //Enter the User ID (UID - Broker Login ID)
ApiKey = "335338b0bbbb9a80be33e64b792784ca"; //Enter the Algomojo API Key
ApiSecret = "b15b9c86bb072fc7cccc3847e50e255e"; //Enter the Algomojo API Secret Key
Broker = "tc"; //Broker Shortcode   'tc' - tradejini cube, 'fs' - firstock 
var Version = "1.0";
var iField ="";
var ApiName = "";
var result = "";


if(Broker == "tc" || Broker == "fs" ){
  ApiName = "ShowQuotes";
  if(Field == "ltp") { iField = "lp"; }
  if(Field == "open") { iField = "o"; }
  if(Field == "high") { iField = "h"; }
  if(Field == "low") { iField = "l"; }
  if(Field == "close") { iField = "lp"; }
  if(Field == "prevclose") { iField = "c"; }
  if(Field == "atp") { iField = "ap"; }
  if(Field == "volume") { iField = "v"; }
  if(Field == "change") { iField = "Change"; }
  if(Field == "perchange") { iField = "PerChange"; }
  if(Field == "oi") { iField = "oi"; }
  if(Field == "change") 
    { 
      var iltp = GetQuote(ApiKey, ApiSecret, Broker, Version,Symbol,Exchange,"lp",ApiName); 
      var iprevclose = GetQuote(ApiKey, ApiSecret, Broker, Version,Symbol,Exchange,"c",ApiName); 
      return (iltp - iprevclose);
      }
    
   if(Field == "perchange") 
    { 
      var iltp = GetQuote(ApiKey, ApiSecret, Broker, Version,Symbol,Exchange,"lp",ApiName); 
      var iprevclose = GetQuote(ApiKey, ApiSecret, Broker, Version,Symbol,Exchange,"c",ApiName); 
      var result = (iltp - iprevclose)*100/iltp;
      result = result.toFixed(2);
      return result;
      } 

  
  
 }


 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 == "tc" || Broker == "fs"){  
  
  apidata = "{\""
      + "uid" + "\":\"" + Uid + "\",\""
      + "exch" + "\":\"" + Exchange + "\",\""
      + "stext" + "\":\"" + Symbol + "\"}";

  }




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

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




  if(Broker == "tc" || Broker == "fs"){  
    result = data[iField];
  }



    return result;
}



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

Field Parameter‘

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

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

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 [email protected]