Sunday, May 31, 2009

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
$SIGNAL_TICKER_LIST = ""
$isAliveCounter = 0
$START_TIME = fnFetchConfigs("START_TIME") ; START_TIME
$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")
Exit
EndIf


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
EndIf
; debugging piece ends..


if fnSignalExists() > 0 Then

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

EndIf

;==================================================
; moved to a seperate PlaceOrder function
;==================================================

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

WEND

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

Else

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

EndIf



Func fnPlaceOrder()

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

$oIE = fnOpenURL("https://secure.icicidirect.com/customer/logon.asp", "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, "https://secure.icicidirect.com/trading/equity/trading_basket_order.asp", 1)

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

Sleep(2000)
_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 = ""

EndIf
Next ; $i



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

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


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

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

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

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

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


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



; signing out
WriteToLogFile ("Signing out.")
fnClickOnLink ("Logout", $oIE)
sleep(7000)
WinActivate("Windows Internet Explorer")
sleep(1000)
SendKeepActive("Windows Internet Explorer")
Send("{ENTER}")
_IEQuit ($oIE)
WriteToLogFile ("Exiting PlaceOrder function.")

EndFunc




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.

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)
$START_TIME = fnFetchConfigs("START_TIME") ; START_TIME
$TICKER_LIST = fnFetchTickerList() ; TICKER_LIST
$REFRESH_INTERVAL = fnFetchConfigs("REFRESH_INTERVAL") ; REFRESH_INTERVAL
$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

$TICKER = $SplitTICKER_LIST[$i]
;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 = ""
$QuoteString=""
$chars = ""

Else

WriteToLogFile ("HTTP Request might have failed!")

EndIf

NEXT ;$i

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

wend

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

Else

WriteToLogFile ("Trading session not yet started.")

EndIf

Exit
;====================================================================================================





Func fnHTTP_GetQuote($Ticker)

Dim $TradeDate, $TradeTime, $LatestQuote

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

_WinHttpSendRequest($h_openRequest)
_WinHttpReceiveResponse($h_openRequest)

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

;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 ================
EndIf

EndIf

_WinHttpCloseHandle($h_openRequest)
_WinHttpCloseHandle($hw_connect)
_WinHttpCloseHandle($hw_open)

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

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

EndFunc





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.

Saturday, May 30, 2009

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

CREATE TABLE ICICI_Configs (
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.

CREATE TABLE ICICI_WatchList (
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,


CREATE TABLE ICICI_Signal (
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,


CREATE TABLE ICICI_Log (
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,

trg_UpdWatchList_BuySignal

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
BEGIN
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);
END

trg_UpdWatchList_SellSignal

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
BEGIN
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);
END


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

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

Testing Google Search Results

Twin Star Adjustable Height Desk, White (ODP1055548D908) https://www.costcobusinessdelivery.com/Twin-Star-Adjustable-Height-Des...