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

In today’s fast-paced world, timely access to accurate data can make all the difference in making informed decisions. For investors and traders in the financial markets, having access to real-time futures and options data, as well as commodities data, can be critical to their success. However, getting this data into a format that is easy to use and manipulate can be a daunting task, especially for those who are not tech-savvy.

Fortunately, Algomojo offers a solution that simplifies the process of getting futures and options data and commodities data into Google Spreadsheet. Algomojo is a powerful platform that provides traders and investors with a wide range of tools and resources for analyzing and managing their trading strategies. In this blog, we will explore how to use Algomojo to get futures and options data and commodities data into Google Spreadsheet, step by step. Whether you are a seasoned trader or just starting, this guide will help you make sense of the data and helps you to configure your own trading application in Google Spreadsheet using Algomojo.

In this tutorial, we are going to use Google Appscript to fetch real-time stock quotes using Algomojo Arrow 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

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 traders who are opening trading accounts with any of the 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, and no strings attached.

Learn How to send Automated Orders using Google Spreadsheet

Google Spreadsheet Configuration

1)Access the Algomojo Google Spreadsheet for Fetching Realtime Data
2)Sheet Name “Realtime” contains the data of stock/index/commodities/currencies with multiple rows. Update the Column A (Trading Instrument) & Column B(Exchange).
3)Sheet Name “Credentials” contains the broker code, apikey, and apisecret. Login to Algomojo to retrieve the apikey and apisecret under MyAPI section.

4)Manual Button “Update Data” is provided to update the real-time data on the press of the button

Here is the Broker shortcode for Algomojo Connected brokers

AB - Alice Blue
AN - Angel Broking
FS - Firstock
FY - Fyers
PT - Paytm
SM - Samco
TC - Tradejini
UP - Upstox
ZB - Zebu
ZE - Zerodha

For details on broker short code refer to the appendix section under Algomojo Arrow API documentation

Open Google Appscript Editor

Goto Extensions -> Appscript to open the appscript editor as shown below

Google Appscript Sample Code to Fetch and Update Stock Prices for Multiple Rows

function updateStockPrice() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Credentials");  
  var broker =sheet.getRange("A2").getValue(); 
  var apiKey = sheet.getRange("B2").getValue(); 
  var apiSecret = sheet.getRange("C2").getValue(); 


  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = spreadsheet.getSheetByName("Realtime");


  var Avals = sheet1.getRange("A2:A").getValues();
  var numRows = Avals.filter(String).length;
  Logger.log(numRows);
for(var i=0;i<numRows;i++){

  //Retrieve the inputs from the cell
  var symbol = sheet1.getRange(2+i,1).getValue(); // get symbol from cell A2
  var exchange = sheet1.getRange(2+i,2).getValue(); // get exchange from cell B2

  var response = GetQuote(apiKey,apiSecret,broker,symbol,exchange);
  var result = JSON.parse(response).data;
 
  //Set the Values to the cell
  sheet1.getRange(2+i,3).setValue(result.last_price); // update cell C2 with stock information
  sheet1.getRange(2+i,4).setValue(result.open); // update cell C2 with stock information
  sheet1.getRange(2+i,5).setValue(result.high); // update cell C2 with stock information
  sheet1.getRange(2+i,6).setValue(result.low); // update cell C2 with stock information
  sheet1.getRange(2+i,7).setValue(result.close); // update cell C2 with stock information
  
}
}

function GetQuote(apiKey,apiSecret,broker,symbol,exchange)
{
  var apitype="GetQuote";
   
  var InTD = "{\"" 
  + "broker" + "\":\"" + broker + "\",\""
  + "symbol" + "\":\"" + symbol + "\",\"" 
  + "exchange" + "\":\"" + exchange + "\"}";

  var result = AMConnect(apiKey, apiSecret, InTD, apitype);
  return result;
  
}

function AMConnect(apikey, apisecret, InTD, apitype)
{
  var Url = "https://amapi.algomojo.com/v1/"+ apitype;
  var postdata = "{\"" + "api_key" + "\":\"" + apikey + "\",\""
                  + "api_secret" + "\":\"" + apisecret + "\",\""
                  + "data" + "\":" + InTD + "}";

  var raw = postdata;

  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();
}

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, Angel, Fyers,Firstock is “Reliance-EQ” and for upstox,zerodha brokers the symbol format is only “RELIANCE”

Supported Exchanges Parameter

Field Parameter (*for programmers only)

The field parameter defines what information about the stock quotes one wants to extract. Following are the supported field parameters used in the Google Appscript

How to Add Triggers to update trading symbol price data automatically?

1)From the Google Spreadsheet goto Extensions -> Appscript ensure that Algomojo.gs appscript is open

2) On 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 the 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

Hope this solves your quote access requirement. If in case you need more details you can send your query to [email protected]