Part III - Order Placement

In Part II - Trade Signal Generation, we looked at how Buy/Sell sigal is generated in the following section I will explain how these signal is picked up and processed.

Here is the AutoIt script to achieve the purpose,

#include <IE.au3>
#include <String.au3>
#include <array.au3>
#Include <Date.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>
#include "myFunctions.au3"

;~ ; Set variables (read from database)
;~ ;Dim $myStartDateTime

Dim $Ticker, $iBasePrc, $chars, $isAliveCounter
$isAliveCounter = 0
$UID = fnFetchConfigs("UID") ; $UID
$PWD = fnFetchConfigs("PWD") ; $PWD
$RUN_TIME = fnFetchConfigs("RUN_TIME") ; RUN_TIME
$CHUNK_AMT = fnFetchConfigs("CHUNK_AMT") ; CHUNK_AMT
$myStartDateTime = fnFetchConfigs("STARTED_AT") ; STARTED_AT ;-------------------- @YEAR & "/" & @MON & "/" & @MDAY & " " & $START_TIME

If $UID="" OR $START_TIME="" OR $RUN_TIME="" OR $CHUNK_AMT="" OR $myStartDateTime="" then
msgbox (0, "Alert", "Application variables are not set properly, EXIT'ing")

If _DateTimeFormat( _NowCalc(), 5) > $START_TIME Then

WriteToLogFile ("New watch cycle Started.")

$minutesRemaining = _DateDiff ( 'n', $myStartDateTime, _NowCalc())

WHILE ($RUN_TIME - $minutesRemaining) >= 0

; this is debugging piece only..
$isAliveCounter = $isAliveCounter + 1
if $isAliveCounter > 360 Then
WriteToLogFile ("Watch cycle is alive.")
$isAliveCounter = 0 ;--reset the counter
; debugging piece ends..

if fnSignalExists() > 0 Then

WriteToLogFile ("Signal found, initiating Order placement")
WriteToLogFile ("Control returned to watching cycle")


; moved to a seperate PlaceOrder function

Sleep(fnFetchConfigs("PLACE_ORDER_REFRESH")) ;Refresh every 5 seconds
$minutesRemaining = _DateDiff ( 'n', $myStartDateTime, _NowCalc())
;ConsoleWrite("Looping... " & $minutesRemaining & @CRLF)


WriteToLogFile ("End of watch cycle, system going to Sleep mode")
;Shutdown(32) ; put computer in Sleep mode


WriteToLogFile ("Trading Session has not yet started!")


Func fnPlaceOrder()

; Site login begins
WriteToLogFile ("Connecting to ICICI Direct.")

$oIE = fnOpenURL("", "4000")
WriteToLogFile ("Logging-in to site.")

$oForm = _IEFormGetObjByName ($oIE, "loginform")
$oElement = _IEFormElementGetObjByName ($oForm, "FML_USR_ID")
_IEFormElementSetValue ($oElement, $UID)

$oElement = _IEFormElementGetObjByName ($oForm, "FML_USR_USR_PSSWRD")
_IEFormElementSetValue ($oElement, $PWD)

$oElement = _IEFormElementGetObjByName($oForm, "Submit1")
_IEAction ($oElement, "click")
Sleep(5000) ; some kind of IE context issue, looking for work around.
_IELoadWait ($oIE)

; Site login Ends

_IENavigate ($oIE, "", 1)

;~ $oIE = _IEAttach ("ICICI :: Trading :: Basket Order", "title")

_IELoadWait ($oIE)

WriteToLogFile ("Placing Order.")

$SplitTICKER_LIST = StringSplit(fnFetchSignalTickerList(), "?")

For $i = 1 to UBound($SplitTICKER_LIST, 1)-2

$OrderValues = StringSplit($SplitTICKER_LIST[$i], "")

$iBasePrc = Round(Number($OrderValues[2]), 1)

If $iBasePrc <> "" Then

$iQty = Int($CHUNK_AMT/$iBasePrc)

if $iQty * $iBasePrc < $CHUNK_AMT then $iQty = $iQty+1

$oForm = _IEFormGetCollection ($oIE, 1)

$oElement = _IEFormElementGetObjByName ($oForm, "FML_ORD_XCHNG_CD" & $i) ; Exchange
_IEFormElementOptionSelect ($oElement, "NSE", 1, "byText")

$oElement = _IEFormElementGetObjByName ($oForm, "FML_ORD_PRDCT_TYP" & $i) ; Product
_IEFormElementOptionSelect ($oElement, "CASH", 1, "byText")

$oElement = _IEFormElementGetObjByName ($oForm, "FML_ACTION" & $i) ; Action
_IEFormElementOptionSelect ($oElement, $OrderValues[3], 1, "byText")

$oElement = _IEFormElementGetObjByName ($oForm, "FML_STCK_CD" & $i) ; Ticker/Stock
_IEFormElementSetValue ($oElement, $OrderValues[1])

$oElement = _IEFormElementGetObjByName ($oForm, "FML_QTY" & $i) ; Quantity
_IEFormElementSetValue ($oElement, $iQty)

_IEFormElementRadioSelect ($oForm, "L", "FML_ORD_TYP" & $i, 1, "byValue")

$oElement = _IEFormElementGetObjByName ($oForm, "FML_ORD_LMT_RT" & $i) ; Limit price
_IEFormElementSetValue ($oElement, $iBasePrc)

fnWriteToDB("update ICICI_Signal set SignalActive = 0 where SignalActive = 1 and SignalTicker='"& $OrderValues[1] &"';")
WriteToLogFile ("Buy signal is de-activitated for " & $OrderValues[1])

$iBasePrc = ""
$Ticker = ""

Next ; $i

;~ it was wasting some time so disabled it
;~ ;Take screenshot after basket it filled
;~ WriteToLogFile ("Order form screenshot - " & TakeScreenShot("ICICI :: Trading :: Basket Order"))
;~ Sleep(1000)

WriteToLogFile ("Order form submitted")
$oElement = _IEFormElementGetObjByName ($oForm, "Submit")
_IEAction ($oElement, "click")
_IELoadWait ($oIE)

;===== Confirmation window
$oForm = _IEFormGetCollection ($oIE, 1)

$oElement = _IEFormElementGetObjByName ($oForm, "CheckUnCheck")
_IEAction ($oElement, "click")

WriteToLogFile ("Order confirmation screenshot - " & TakeScreenShot("ICICI :: Trading :: Basket Order Verification"))

$oElement = _IEFormElementGetObjByName ($oForm, "Submit_Butt")
_IEAction ($oElement, "click")
WriteToLogFile ("Order Confirmation done.")

_IELoadWait ($oIE)
WriteToLogFile ("Confirmation submitted screenshot - " & TakeScreenShot("ICICI :: Trading :: Basket Order Acknowledgement"))
;===== Confirmation window Ends

;~ msgbox (0, "CAUTION", "Hard exit ! next")
;~ Exit

; signing out
WriteToLogFile ("Signing out.")
fnClickOnLink ("Logout", $oIE)
WinActivate("Windows Internet Explorer")
SendKeepActive("Windows Internet Explorer")
_IEQuit ($oIE)
WriteToLogFile ("Exiting PlaceOrder function.")


As I said before I am not going to explain this script in plain english as its already simple, this is the reason I recommend AutoIt as the best hobbyist programmer language

What about the order quantity ? - The order quantity is based on CHUNK_AMOUNT variable set in ICICI_Configs table. It will check how many units it can buy with
CHUNK_AMOUNT/OrderPrice rounded off to integer. This allows buying any stock in chunks rather than in one single go.

Ones the order placement is done it will update the YstClosingQuote value for the respective ticker/symbol. Look at ICICI_Log table screenshot in Part II to get better understanding on sequence of activities.

Labels: , ,

Posted by Technology Yogi, Sunday, May 31, 2009 May 31, 2009 | 0 comments |

Part II - Trade Signal Generation

By now you know how the database is layed out. In the following parts you will be mostly dealing with AutoIt script which intracts with market data and my database with configurations and watch list.

I will put all of the source code here instead of explaining it in plain english. Since the AutoIt is BASIC like language it easy to understand.

#include <ie.au3>
#include <string.au3>
#include <array.au3>
#Include <date.au3>
#include <sqlite.au3>
#include <sqlite.dll.au3>
#include "myFunctions.au3"
#include <winhttp.au3>

; Set variables (read from database)
$TICKER_LIST = fnFetchTickerList() ; TICKER_LIST
$RUN_TIME = fnFetchConfigs("RUN_TIME") ; RUN_TIME
$myStartDateTime = fnFetchConfigs("STARTED_AT") ; STARTED_AT
;-------------------- @YEAR & "/" & @MON &
; "/" & @MDAY & " " & $START_TIME

If _DateTimeFormat( _NowCalc(), 5) > $START_TIME Then

$MinutesSinceStarted = _DateDiff ( 'n', $myStartDateTime, _NowCalc())
WriteToLogFile ("Started capturing quotes.")

Dim $iBasePrc, $chars, $QuoteFileName
Dim $AppEndDateTime, $QuoteString

while ($RUN_TIME - $MinutesSinceStarted) >= 0

;split the $TICKER_LIST and get quotes for all the tickers in the list
$SplitTICKER_LIST = StringSplit($TICKER_LIST, "")

for $i = 1 to UBound($SplitTICKER_LIST, 1)-1

;msgbox (0, $i, $SplitTICKER_LIST[$i])

$QuoteString = fnHTTP_GetQuote($SplitTICKER_LIST[$i])

if StringLen($QuoteString) > 10 Then

$QuoteArray = _StringSplit($QuoteString, "")

$CurrentQuote = StringReplace ($QuoteArray[2], ",", "")
$myTime = $QuoteArray[0] & " " & $QuoteArray[1]

$iBasePrc = Number($iBasePrc)
$CurrentQuote = Number($CurrentQuote)

fnWriteToDB ("UPDATE ICICI_WatchList SET &_ CurrentQuote = "& $CurrentQuote &", &_
LocalDateTime = CURRENT_TIMESTAMP WHERE Ticker = '" & $TICKER & "';")
;ConsoleWrite ("Quotes updated.." & @CRLF)

$iBasePrc = ""
$chars = ""


WriteToLogFile ("HTTP Request might have failed!")


NEXT ;$i

sleep ($REFRESH_INTERVAL) ; every 20 seconds..
$MinutesSinceStarted = _DateDiff ( 'n', $myStartDateTime, _NowCalc())
;ConsoleWrite($RUN_TIME - $MinutesSinceStarted & @CRLF)


WriteToLogFile ("End of trading session.")
;Shutdown(32) ; put computer in Sleep mode


WriteToLogFile ("Trading session not yet started.")



Func fnHTTP_GetQuote($Ticker)

Dim $TradeDate, $TradeTime, $LatestQuote

$hw_open = _WinHttpOpen()
$hw_connect = _WinHttpConnect($hw_open, "")
$h_openRequest = _WinHttpOpenRequest($hw_connect, "GET", "/trading/equity/trading_stock_quote.asp?Symbol=" & $Ticker)


If _WinHttpQueryDataAvailable($h_openRequest) Then
$data = ""
While 1
$chunk = _WinHttpReadData($h_openRequest, 1)
If Not @extended Then ExitLoop
;ConsoleWrite($chunk & @CRLF)
$data &= $chunk

;ConsoleWrite($data & @CRLF)
;msgbox (0, "StringLen($data)", StringLen($data))
;-- error stringLen 7808
;-- good stringLen 12995

If StringLen($data) > 10000 Then ; good request return

;=========== LAST TRDED DATE START ================
;First cut
$mySubString = "DATE"
$StartAt = StringInStr($data, $mySubString)
$TradeDate = StringMid($data, $StartAt, 200)

;Second cut
$mySubString = ""
$StartAt = StringInStr($TradeDate, $mySubString)
$TradeDate = StringMid($TradeDate, $StartAt, 200)

;Third cut
$mySubString = ">"
$StartAt = StringInStr($TradeDate, $mySubString)
$TradeDate = StringMid($TradeDate, $StartAt+1, 200)

;Fourth cut
$mySubString = ""
$EndAt = StringInStr($TradeDate, $mySubString)
$TradeDate = StringMid($TradeDate, 1, $EndAt-1)

;msgbox (0, "Last Trade Date", $TradeDate)
;=========== LAST TRDED DATE ENDS ================

;=========== LAST TRDED TIME START ================
;First cut
$mySubString = "LAST TRADED "
$StartAt = StringInStr($data, $mySubString)
$TradeTime = StringMid($data, $StartAt, 200)

;Second cut
$mySubString = ""
$StartAt = StringInStr($TradeTime, $mySubString)
$TradeTime = StringMid($TradeTime, $StartAt, 200)

;Third cut
$mySubString = ">"
$StartAt = StringInStr($TradeTime, $mySubString)
$TradeTime = StringMid($TradeTime, $StartAt+1, 200)

;Fourth cut
$mySubString = ""
$EndAt = StringInStr($TradeTime, $mySubString)
$TradeTime = StringMid($TradeTime, 1, $EndAt-1)
;msgbox(0, "Latest Trde Time", $TradeTime)
;=========== LAST TRDED TIME ENDS ================

;~ ;=========== LASTEST QUOTES STARTS ================
;First cut
$mySubString = " LAST TRADE "
$StartAt = StringInStr($data, $mySubString)
$LatestQuote = StringMid($data, $StartAt, 200)

;Second cut
$mySubString = ""
$StartAt = StringInStr($LatestQuote, $mySubString)
$LatestQuote = StringMid($LatestQuote, $StartAt, 200)

;Third cut
$mySubString = ">"
$StartAt = StringInStr($LatestQuote, $mySubString)
$LatestQuote = StringMid($LatestQuote, $StartAt+1, 200)

;Fourth cut
$mySubString = ""
$EndAt = StringInStr($LatestQuote, $mySubString)
$LatestQuote = StringMid($LatestQuote, 1, $EndAt-1)
;msgbox(0, "Latest Quoted", $LatestQuote)
;~ ;=========== LASTEST QUOTES ENDS ================



;ConsoleWrite ($TradeDate & "" & $TradeTime & "" & $LatestQuote & @CRLF)

Return($TradeDate & "" & $TradeTime & "" & $LatestQuote)


This above code is the one which captures the ticker/stock price and writes it to the ICICI_Watchlist >> CurrentQuote column. As soon as the column is updated
with new price the trigger on that table fires up and checks if the price is
closer by the expected target price (QuoteVariance), if yes it will generate
a signal.

Is the signal buy or sell ? - this depends on QuoteVariance. If QuoteVariance
is set as minus that means you are looking for price going lower and lower, so
BUY signal is generated for PreviousClosing-(PreviousClosing*QuoteVariance)

Similarly if the you are looking to sell your holdings if price goes above +5% (QuoteVariance) of previous day closing then SELL signal is generated

In the next part, I will explain how this signal is processed and order is actual placed.

Labels: , ,

Posted by Technology Yogi, May 31, 2009 | 2 comments |

PART I - Database (Stock Trader)

I am assuming you have read the assumptions and about the requirement of this program in the start-up page.

Let me start with database design piece here in this article. We have put up 5 tables. ICICI_Configs, ICICI_WatchList, ICICI_Signal, ICIC_Log and ICICI_Holiday

Table - ICICI_Configs:

This table holds the application configurations like when to start, how long to watch the market, the user and password for the a/c to place order.

Below is the simple table structure

ConfigID integer primary key autoincrement,
ConfigVariableDesc varchar(500),
ConfigVariable varchar(30) not null,
ConfigValue varchar(100) not null

And here is the data how it looks like in my case,

Note: START_TIME and STARTED_AT variables are to be set to your local timings like i.e. if you are in India it should be set to 09:55:00, but if you are in United states its 21:25:00

Table - ICICI_WatchList:

This is where I store my stock ticker to watch (that I am interested in). With price target percentage movement values.

Ticker char(6) primary key not null,
YstClosingQuote numeric (20, 4),
CurrentQuote numeric (20, 4),
QuoteVariance numeric (6, 2),
isActive bit default 0, -- if 1 collect quote for this TICKER
LocalDateTime DateTime default CURRENT_TIMESTAMP

Here is the how the data would look like...

Table - ICICI_Signal:

In this table I put the buy/sell signal record, whenever this is a ACTIVE signal in this table the order placement thread (program) will start its real action. Following is the table structure for the same,

SignalTicker varchar(10) primary key not null,
SignalPrice numeric(20, 4),
CurrentPrice numeric(20, 4),
SignalActive bit,
SignalDateTime datetime default CURRENT_TIMESTAMP,
TrnType CHAR(4)

Below is how the data would look like,

When there is an active record i.e. SignalActive = 1, the Order Placement theread will start logging in to ICICI Direct site and place order. While placing the order it will pick up the lower of the SignalPrice or CurrentPrice. The CurrentPrice will be continuosly updated until the Signal is marked inactive i.e. SignalActive = 1 so that while order placement happens and the Current trading price of a scrip/ticker goes down it can take advantage of that situation.

Table - ICICI_Log:

ICICI_Log is a logging table, where application writes about activity it has taken. Here is the table structure and sample data,

Id INTEGER primary key autoincrement,
ThreadName varchar(100),
LogDesc varchar(1000),
LogDate datetime default CURRENT_TIMESTAMP

The above figure should tell you how the things work. Apart from logging the detail the application also captures the images/screenshot. These screenshot can be helpfull in case if there is any issue with the site.

Now let me explain how the signal generation works. ICICI_WatchList has two triggers as follows,


CREATE TRIGGER trg_UpdWatchList_BuySignal UPDATE OF CurrentQuote ON ICICI_WatchList
WHEN (new.CurrentQuote <= (Old.YstClosingQuote+(Old.YstClosingQuote*Old.QuoteVariance))*1.005 ) and Old.isActive = 1 and Old.QuoteVariance < 0
INSERT OR REPLACE INTO ICICI_Signal VALUES (Old.Ticker, (Old.YstClosingQuote + (Old.YstClosingQuote*Old.QuoteVariance)), New.CurrentQuote, 1, Current_TimeStamp, 'BUY');
UPDATE ICICI_WatchList SET YstClosingQuote = (Old.YstClosingQuote + (Old.YstClosingQuote*Old.QuoteVariance)) WHERE Ticker = Old.Ticker;
INSERT INTO ICICI_Log (ThreadName, LogDesc, LogDate) values ('BUY TRIGGER', 'Updated signal value for ' || Old.Ticker || ' (' || new.CurrentQuote ||')', current_timestamp);


CREATE TRIGGER trg_UpdWatchList_SellSignal UPDATE OF CurrentQuote ON ICICI_WatchList
WHEN (new.CurrentQuote >= (Old.YstClosingQuote+(Old.YstClosingQuote*Old.QuoteVariance))*0.995 ) and Old.isActive = 1 and Old.QuoteVariance > 0
INSERT OR REPLACE INTO ICICI_Signal VALUES (Old.Ticker, (Old.YstClosingQuote + (Old.YstClosingQuote*Old.QuoteVariance)), New.CurrentQuote, 1, Current_TimeStamp, 'SELL');
UPDATE ICICI_WatchList SET YstClosingQuote = (Old.YstClosingQuote + (Old.YstClosingQuote*Old.QuoteVariance)) WHERE Ticker = Old.Ticker;
INSERT INTO ICICI_Log (ThreadName, LogDesc, LogDate) values ('SELL TRIGGER', 'Updated signal value for ' || Old.Ticker || ' (' || new.CurrentQuote ||')', current_timestamp);

If you look at the trigger very closely you will see that when the trading price is +/- 0.5% range the signal is generated, this is because I wanted to ensure that the order does not get rejected due to out of range pricing.

This is all about database side implementation, let check what is there in AutoIt scripting side in Part II - Market Watch

Labels: ,

Posted by Technology Yogi, Saturday, May 30, 2009 May 30, 2009 | 0 comments |

My little stock trader.

This one is my latest and favourite little program out of my hobby programming. And is about stock trading specific to INDIAN market and the stock broker is ICICI Direct in this case. Technical requirements - Windows OS, Internet Explorer 6 (and above), AutoIt and SQLite.

What is it after all ? - this is a little program that I wrote to do stock trading. When I wrote this program the requirements were I wanted someone to watch the market (specific stocks about 10 tickers) and buy/sell based on price movement. Eg. if Infosys technologies stock is up by 7% want to sell out my holding in Infosys OR assume that I want to hold more stocks of Bharti Airtel but only if it goes below by 7%. Currently you can specify the target prices in +/- percentage only.

This simple thing can be done by placing limit orders isn't ? - NO, not atleast in ICICI Direct. You cannot place limt orders beyound +/- 5%, even during the market hrs. If you are with other stock brokers may be you have a choice.

This program uses Autoit - my obvious choice for hobby programming (atleast for now) and SQLite - combination of these is too great. Earlier I never thought I could write such good/powerfull program using these two piece of softwares, but indeed it worked out well - thanks to AutoIt and it support for SQLite, plus their incredibly simple documentation with good working samples.

This program is also a great sample application for learning AutoIt and SQLite :)

For the simplicity I have split this article into 3 sections as follows,

PART I - Database
PART II - Market Watching
PART - III - Order Placement

Labels: ,

Posted by Technology Yogi, May 30, 2009 | 0 comments |