Excel

OpenAlgo Excel Add-In

Overview

OpenAlgo is an Excel Add-In that provides seamless integration with the OpenAlgo API for algorithmic trading. This add-in allows users to fetch market data, place and manage orders, retrieve historical data, and stream real-time market data directly from Excel.

Features

• Account Management: Retrieve funds, order books, trade books, and position books.

• Market Data: Fetch real-time market quotes, depth, historical data, and available time intervals.

• Order Management: Place, modify, cancel, and retrieve order statuses.

• Smart & Basket Orders: Execute split, smart, and bulk orders.

• Risk Management: Close all open positions for a given strategy.

• WebSocket Streaming: Real-time market data streaming with support for LTP, Quote, and Depth modes.

• Persistent Configuration: API key and settings are saved to disk and auto-loaded on Excel restart.

Prerequisites

• .NET 8.0 Desktop Runtime installed

• Excel-DNA Add-In (included in the project dependencies)

• Microsoft Excel (Office 365 recommended)

Install the OpenAlgo Excel Add-In

Before installing, ensure you are selecting the correct version based on your Excel installation.

Steps to Check Your Excel Version

1. Open Microsoft Excel

2. Click File > Account

3. Click About Excel

4. Look for 32-bit or 64-bit in the version details.

Which Version Should You Install?

• If your Excel version is 64-bit > Install the 64-bit add-in (Recommended)

• If your Excel version is 32-bit > Install the 32-bit add-in

Download the OpenAlgo Excel Add-In: GitHub Releases

.NET 8 Desktop Runtime is Required

OpenAlgo Excel Add-In is built using Excel-DNA, which requires the .NET 8 Desktop Runtime to run.

If the add-in is not working or Excel does not recognize it, install the .NET 8 Desktop Runtime from: Download .NET 8 Desktop Runtime

After installing the runtime, restart your system and try loading the add-in again.

---

Configuration

Setting API Key, Version, and Host URL

Function: oa_api(api_key, [version], [host_url])

This function must be called once to configure the API connection. The configuration is persisted to disk at %LOCALAPPDATA%\OpenAlgo\config.json, so you only need to call it once. On subsequent Excel sessions, the saved API key is automatically loaded.

All other functions use these stored credentials.

Parameters:

Parameter	Required	Default	Description
api_key	Yes	-	API key for authentication
version	No	"v1"	API version
host_url	No	"http://127.0.0.1:5000"	OpenAlgo server URL

Example Usage:

=oa_api("your_api_key")
=oa_api("your_api_key", "v1", "http://127.0.0.1:5000")

---

Account Functions

Retrieve Funds

Function: oa_funds()

Returns a table with available funds and margin details (available cash, collateral, M2M realized/unrealized, utilised debits).

=oa_funds()

---

Retrieve Order Book

Function: oa_orderbook()

Returns a table with all orders placed during the session (Symbol, Action, Exchange, Quantity, Order Status, Order ID, Price, Price Type, Trigger Price, Product, Timestamp).

=oa_orderbook()

---

Retrieve Trade Book

Function: oa_tradebook()

Returns a table with all executed trades (Symbol, Exchange, Action, Quantity, Product, Timestamp, Trade Value, Average Price, Order ID).

=oa_tradebook()

---

Retrieve Position Book

Function: oa_positionbook()

Returns a table with all open positions (Symbol, Exchange, Quantity, Product, Average Price).

=oa_positionbook()

---

Retrieve Holdings

Function: oa_holdings()

Returns a table with all holdings in the demat account (Symbol, Exchange, Quantity, Product, Pnl, Pnl Percent).

=oa_holdings()

---

Market Data Functions

Get Market Quotes

Function: oa_quotes(symbol, exchange)

Parameter	Required	Description
symbol	Yes	Trading symbol
exchange	Yes	Exchange (e.g., NSE, BSE, NFO, MCX)

Returns market price details for the given symbol (LTP, open, high, low, close, volume, etc.).

=oa_quotes("RELIANCE", "NSE")

---

Get Market Depth

Function: oa_depth(symbol, exchange)

Parameter	Required	Description
symbol	Yes	Trading symbol
exchange	Yes	Exchange

Returns order book depth showing LTP, Volume, Ask Price/Quantity, and Bid Price/Quantity levels.

=oa_depth("RELIANCE", "NSE")

---

Fetch Historical Data

Function: oa_history(symbol, exchange, interval, start_date, end_date)

Parameter	Required	Description
symbol	Yes	Trading symbol
exchange	Yes	Exchange
interval	Yes	Candle interval (e.g., "1m", "5m", "15m", "1d")
start_date	Yes	Start date in YYYY-MM-DD format
end_date	Yes	End date in YYYY-MM-DD format

Returns historical OHLCV data in a table (Ticker, Date, Time IST, Open, High, Low, Close, Volume).

=oa_history("RELIANCE", "NSE", "1m", "2024-12-01", "2024-12-31")

---

Get Supported Intervals

Function: oa_intervals()

Returns a list of all supported candle intervals grouped by category (Seconds, Minutes, Hours, Days, Weeks, Months).

=oa_intervals()

---

Order Functions

Place an Order

Function: oa_placeorder(strategy, symbol, action, exchange, pricetype, product, [quantity], [price], [trigger_price], [disclosed_quantity])

Parameter	Required	Description
strategy	Yes	Strategy name
symbol	Yes	Trading symbol
action	Yes	"BUY" or "SELL"
exchange	Yes	Exchange (e.g., NSE, BSE, NFO, MCX)
pricetype	Yes	"MARKET", "LIMIT", "SL", "SL-M"
product	Yes	"MIS", "CNC", "NRML"
quantity	No	Number of shares/lots (default: 0)
price	No	Limit price (default: 0)
trigger_price	No	Trigger price (default: 0)
disclosed_quantity	No	Disclosed quantity (default: 0)

=oa_placeorder("MyStrategy", "INFY", "BUY", "NSE", "LIMIT", "MIS", 10, 1500, 0, 0)

---

Place a Smart Order

Function: oa_placesmartorder(strategy, symbol, action, exchange, pricetype, product, [quantity], [position_size], [price], [trigger_price], [disclosed_quantity])

Smart orders automatically manage position sizing based on the current position.

Parameter	Required	Description
strategy	Yes	Strategy name
symbol	Yes	Trading symbol
action	Yes	"BUY" or "SELL"
exchange	Yes	Exchange
pricetype	Yes	"MARKET", "LIMIT", "SL", "SL-M"
product	Yes	"MIS", "CNC", "NRML"
quantity	No	Number of shares/lots (default: 0)
position_size	No	Target position size (default: 0)
price	No	Limit price (default: 0)
trigger_price	No	Trigger price (default: 0)
disclosed_quantity	No	Disclosed quantity (default: 0)

=oa_placesmartorder("SmartStrat", "INFY", "BUY", "NSE", "MARKET", "MIS", 10, 0, 0, 0, 0)

---

Place a Basket Order

Function: oa_basketorder(strategy, orders)

Place multiple orders at once by referencing an Excel range containing order details.

Parameter	Required	Description
strategy	Yes	Strategy name
orders	Yes	Excel range reference containing order rows

The referenced range must contain 9 columns in this exact order:

Column	1	2	3	4	5	6	7	8	9
Field	Symbol	Exchange	Action	Quantity	PriceType	Product	Price	Trigger Price	Disclosed Quantity

=oa_basketorder("MyStrategy", A2:I5)

---

Place a Split Order

Function: oa_splitorder(strategy, symbol, action, exchange, [quantity], [splitsize], [pricetype], [product], [price], [trigger_price], [disclosed_quantity])

Splits a large order into smaller chunks to reduce market impact.

Parameter	Required	Default	Description
strategy	Yes	-	Strategy name
symbol	Yes	-	Trading symbol
action	Yes	-	"BUY" or "SELL"
exchange	Yes	-	Exchange
quantity	No	0	Total order quantity
splitsize	No	0	Size of each split chunk
pricetype	No	"MARKET"	Order type
product	No	"MIS"	Product type
price	No	0	Limit price
trigger_price	No	0	Trigger price
disclosed_quantity	No	0	Disclosed quantity

=oa_splitorder("MyStrategy", "RELIANCE", "BUY", "NSE", 100, 10, "MARKET", "MIS", 0, 0, 0)

---

Modify an Order

Function: oa_modifyorder(strategy, orderid, symbol, action, exchange, [quantity], [pricetype], [product], [price], [trigger_price], [disclosed_quantity])

Parameter	Required	Default	Description
strategy	Yes	-	Strategy name
orderid	Yes	-	Order ID to modify
symbol	Yes	-	Trading symbol
action	Yes	-	"BUY" or "SELL"
exchange	Yes	-	Exchange
quantity	No	0	New quantity
pricetype	No	"MARKET"	New order type
product	No	"MIS"	New product type
price	No	0	New limit price
trigger_price	No	0	New trigger price
disclosed_quantity	No	0	New disclosed quantity

=oa_modifyorder("MyStrategy", "241700000023457", "RELIANCE", "BUY", "NSE", 1, "LIMIT", "MIS", 2500, 0, 0)

---

Cancel an Order

Function: oa_cancelorder(strategy, orderid)

Parameter	Required	Description
strategy	Yes	Strategy name
orderid	Yes	Order ID to cancel

=oa_cancelorder("MyStrategy", "241700000023457")

---

Cancel All Orders

Function: oa_cancelallorder(strategy)

Cancels all open orders for the given strategy.

Parameter	Required	Description
strategy	Yes	Strategy name

=oa_cancelallorder("MyStrategy")

---

Close All Open Positions

Function: oa_closeposition(strategy)

Parameter	Required	Description
strategy	Yes	Strategy name

=oa_closeposition("MyStrategy")

---

Get Order Status

Function: oa_orderstatus(strategy, orderid)

Parameter	Required	Description
strategy	Yes	Strategy name
orderid	Yes	Order ID to check

=oa_orderstatus("MyStrategy", "241700000023457")

---

Get Open Position

Function: oa_openposition(strategy, symbol, exchange, product)

Parameter	Required	Description
strategy	Yes	Strategy name
symbol	Yes	Trading symbol
exchange	Yes	Exchange
product	Yes	Product type (MIS, CNC, NRML)

=oa_openposition("MyStrategy", "INFY", "NSE", "MIS")

---

WebSocket Functions (Real-Time Streaming)

WebSocket functions provide real-time streaming market data directly in Excel cells. The add-in handles connection, authentication, and subscription automatically.

How It Works

1. Call =oa_api("your_key") once to set your API key (persisted to disk for future sessions)

2. Use any WebSocket data function - it auto-connects and auto-subscribes

3. Cells update continuously in real-time

Connection Management

Connect to WebSocket

Function: oa_ws_connect([url])

Connects to the WebSocket server and authenticates. If the API key was previously set via oa_api(), the connection authenticates automatically. The function waits for the API key if oa_api() hasn't been evaluated yet during recalculation.

Parameter	Required	Default	Description
url	No	"ws://127.0.0.1:8765"	WebSocket server URL

=oa_ws_connect()
=oa_ws_connect("ws://127.0.0.1:8765")
=oa_ws_connect("wss://yourdomain.com/ws")

---

Get Connection Status

Function: oa_ws_status()

Returns the current WebSocket connection state (Open, Closed, Connecting, Disconnected).

=oa_ws_status()

---

Data Functions (Auto-Subscribe)

These volatile functions update continuously in real-time. They automatically connect to the WebSocket server and subscribe to the required data feed if not already subscribed.

LTP (Last Traded Price) - Streaming

Function: oa_ws_ltp(symbol, exchange)

Parameter	Required	Description
symbol	Yes	Trading symbol
exchange	Yes	Exchange

Returns a real-time last traded price value that updates automatically.

=oa_ws_ltp("RELIANCE", "NSE")

---

Quote - Streaming

Function: oa_ws_quote(symbol, exchange)

Parameter	Required	Description
symbol	Yes	Trading symbol
exchange	Yes	Exchange

Returns a real-time market quote table (LTP, open, high, low, close, volume, etc.) that updates automatically.

=oa_ws_quote("RELIANCE", "NSE")

---

Depth - Streaming

Function: oa_ws_depth(symbol, exchange, [depth_level])

Parameter	Required	Default	Description
symbol	Yes	-	Trading symbol
exchange	Yes	-	Exchange
depth_level	No	5	Number of depth levels to display

Returns a real-time order book depth table showing Bid Orders, Bid Qty, Bid Price, LTP, Ask Price, Ask Qty, Ask Orders.

=oa_ws_depth("RELIANCE", "NSE")
=oa_ws_depth("RELIANCE", "NSE", 5)

---

Get Specific Field - Streaming

Function: oa_ws_field(symbol, exchange, field, [mode])

Retrieve a specific data field from the WebSocket stream.

Parameter	Required	Default	Description
symbol	Yes	-	Trading symbol
exchange	Yes	-	Exchange
field	Yes	-	Field name (e.g., "ltp", "open", "high", "low", "close", "volume", "change_percent")
mode	No	2	Data mode: 1=LTP, 2=Quote, 3=Depth

=oa_ws_field("RELIANCE", "NSE", "ltp")
=oa_ws_field("RELIANCE", "NSE", "volume", 2)

---

Subscription Management

Data functions auto-subscribe, so manual subscription management is optional. Use these when you need fine-grained control.

Subscribe to WebSocket Feed

Function: oa_ws_subscribe(symbol, exchange, mode, [depth_level])

Parameter	Required	Description
symbol	Yes	Trading symbol
exchange	Yes	Exchange
mode	Yes	Subscription mode (1=LTP, 2=Quote, 3=Depth)
depth_level	No	Number of depth levels (only for mode 3)

=oa_ws_subscribe("RELIANCE", "NSE", 1)
=oa_ws_subscribe("RELIANCE", "NSE", 3, 5)

---

Unsubscribe from WebSocket Feed

Functions:

Function	Description
oa_ws_unsubscribe(symbol, exchange, mode)	Unsubscribe from a specific mode
oa_ws_unsubscribe_ltp(symbol, exchange)	Unsubscribe from LTP data
oa_ws_unsubscribe_quote(symbol, exchange)	Unsubscribe from Quote data
oa_ws_unsubscribe_depth(symbol, exchange)	Unsubscribe from Depth data
oa_ws_unsubscribe_all()	Unsubscribe from all feeds

=oa_ws_unsubscribe("RELIANCE", "NSE", 1)
=oa_ws_unsubscribe_ltp("RELIANCE", "NSE")
=oa_ws_unsubscribe_quote("RELIANCE", "NSE")
=oa_ws_unsubscribe_depth("RELIANCE", "NSE")
=oa_ws_unsubscribe_all()

---

View Active Subscriptions

Function: oa_ws_subscriptions()

Returns a table of all active WebSocket subscriptions.

=oa_ws_subscriptions()

---

Debug WebSocket Data

Function: oa_ws_debug(symbol, exchange, mode)

Shows subscription status and cached data for debugging purposes.

Parameter	Required	Description
symbol	Yes	Trading symbol
exchange	Yes	Exchange
mode	Yes	Data mode (1=LTP, 2=Quote, 3=Depth)

=oa_ws_debug("RELIANCE", "NSE", 2)

---

WebSocket Data Modes

Mode	Name	Data Includes
1	LTP	Last traded price, timestamp
2	Quote	OHLC, LTP, volume, change %, open interest
3	Depth	Full order book with bid/ask price, quantity, and orders

---

WebSocket Quick Start Example

' Step 1: Set API key (only needed once, persisted to disk)
=oa_api("your_api_key")

' Step 2: Connect to WebSocket
=oa_ws_connect()

' Step 3: Use data functions - they auto-subscribe
Cell A1: =oa_ws_ltp("RELIANCE", "NSE")
Cell A2: =oa_ws_ltp("TCS", "NSE")
Cell A3: =oa_ws_ltp("INFY", "NSE")
Cell B1: =oa_ws_field("RELIANCE", "NSE", "volume", 2)
Cell B2: =oa_ws_field("TCS", "NSE", "volume", 2)
Cell B3: =oa_ws_field("INFY", "NSE", "volume", 2)

' All cells update continuously in real-time

' Step 4: Clean up when done
=oa_ws_unsubscribe_all()

---

Debugging and Logs

• If functions return #VALUE!, check if API credentials are set correctly using =oa_api().

• Ensure the OpenAlgo backend is running and accessible at the configured Host URL.

• Ensure the OpenAlgo WebSocket server is running (default: ws://127.0.0.1:8765).

• WebSocket logs are written to %LOCALAPPDATA%\OpenAlgo\websocket.log.

• Configuration is saved at %LOCALAPPDATA%\OpenAlgo\config.json.

Notes

• Call =oa_api("your_api_key") once to configure the API connection. The key is persisted and auto-loaded on future Excel sessions.

• Test in OpenAlgo Analyzer Mode before using in live markets.

• All order functions use strategy as the first parameter for consistency.

• By default, missing optional parameters in order functions default to 0.

• WebSocket data functions (oa_ws_ltp, oa_ws_quote, oa_ws_depth, oa_ws_field) are volatile and recalculate automatically.

Support and Contributions

• Issues: Report issues at the repository's issue tracker.

• Contributions: PRs are welcome to improve features, documentation, or bug fixes.

• License: OpenAlgo is open-source and distributed under the AGPL-3.0 License.

References

• OpenAlgo API Docs

• Excel-DNA Documentation

Disclaimer

The creators of this add-in are not responsible for any issues, losses, or damages that may arise from its use. It is strongly recommended to test all functionalities in OpenAlgo Analyzer Mode before applying them to live trading. Always verify API responses and exercise caution while executing trades.