Rajandran R Telecom Engineer turned Full-time Derivative Trader. Mostly Trading Nifty, Banknifty, USDINR and High Liquid Stock Derivatives. Trading the Markets Since 2006 onwards. Using Market Profile and Orderflow for more than a decade. Designed and published 100+ open source trading systems on various trading tools. Strongly believe that market understanding and robust trading frameworks are the key to the trading success. Writing about Markets, Trading System Design, Market Sentiment, Trading Softwares & Trading Nuances since 2007 onwards. Author of Marketcalls.in and Co-Creator of Algomojo (Algorithmic Trading Platform for DIY Traders)

How to Fetch Upstox Historical Intraday API Data into Excel sheet using Microsoft Power Query

1 min read

In the previous session, we learned how Algomojo users can Get Free Upstox Interactive API and Historical Data API and in this tutorial, we are going to learn how to use Historical Intraday API, how to test the historical data API, and how to fetch Upstox Historical Intraday API Data into Excel sheet using Microsoft power query.

How to Test using Postman

Postman is an innovative tool to understand the API and In Algomojo we use Postman a lot to test and deploy robust API solutions for algotrading. Here is a video tutorial that explains how to use Postman to test Algomojo API to fetch historical data and helps understand the API data format much better before designing your trading strategies using the APIs.

Fetch Upstox Historical Intraday API Data into Excel sheet

Once you test the Historical API then next solution is to bring the historical data API into excel. Inorder to bring a Rest API with post method it is highly recommended to use Microsoft power query to fetch the historical data.

Requirements

Requirements

1)Algomojo – Upstox Trading Account
2)Upstox API
3)Microsoft Office 2013, Office 365 or Higher Version

What is a Power Query?

According to Microsoft portal, Power Query is a data connection technology that enables you to discover, connect, combine, and refine data sources to meet your analysis needs. Features in Power Query are available in Excel and Power BI Desktop.

Video Tutorial on Fetching Free Upstox API data

Microsoft Power Query

let
    url = "https://upapi.algomojo.com/1.0/Historical",
    body = "{
    ""api_key"":""8dedb0aae13f84107fa22651e4c53284"",
    ""api_secret"":""8bc7d9166092522db22da9c345345345"",
    ""data"":
      {
        ""symbol"":""RELIANCE"",
        ""exchange"":""NSE_EQ"",
        ""interval"":""5"",
        ""start_date"":""24-01-2021"",
        ""end_date"":""29-01-2021"",
        ""format"":""json""
      }
}",
    Source = Json.Document(Web.Contents(url, [Headers=[#"Content-Type"="application/json"],Content =Text.ToBinary(body)])),
    data = Source[data],
    #"Converted to Table" = Table.FromList(data, Splitter.SplitTextByDelimiter(","), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", Int64.Type}, {"Column2", type number}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "DateTime"}, {"Column2", "Open"}, {"Column3", "High"}, {"Column4", "Low"}, {"Column5", "Close"}, {"Column6", "Volume"}}),
    #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Timestamp", each #datetime(1970,1,1,0,0,0) + #duration(0, 5,30, [DateTime]/1000)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"DateTime"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"Timestamp", "Open", "High", "Low", "Close", "Volume"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"Timestamp", "DateTime"}})
in
    #"Renamed Columns1"

Download the Excel Sheet with PowerQuery to Fetch Upstox Historical Data

Rajandran R Telecom Engineer turned Full-time Derivative Trader. Mostly Trading Nifty, Banknifty, USDINR and High Liquid Stock Derivatives. Trading the Markets Since 2006 onwards. Using Market Profile and Orderflow for more than a decade. Designed and published 100+ open source trading systems on various trading tools. Strongly believe that market understanding and robust trading frameworks are the key to the trading success. Writing about Markets, Trading System Design, Market Sentiment, Trading Softwares & Trading Nuances since 2007 onwards. Author of Marketcalls.in and Co-Creator of Algomojo (Algorithmic Trading Platform for DIY Traders)

Line Trading – Algomojo Automation Module for Amibroker Users

The line Trading Automation tool is designed for Manual traders who want to perform level-based trade execution faster and also bring some advanced trade...
Rajandran R
58 sec read

Mini Certification Course on Algorithmic Trading Strategies

If you are new to Automated trading? This certification course will help you to kickstart your automated trading with your broker and get to...
Rajandran R
1 min read

AllinOneAlerts – Amibroker Alerts Module for Amibroker Users

AllinOneAlerts is one of the most important and most requested modules to bring all sorts of alerts with no extra coding efforts. Now even...
Rajandran R
5 min read

2 Replies to “How to Fetch Upstox Historical Intraday API Data into…”

  1. Hi! What is the maximum amount of historical data we can get? Do they provide historical options data as well?

Leave a Reply

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