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.

Labels: , ,

Posted by Technology Yogi, Sunday, May 31, 2009 7:53 AM | 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

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

Labels: ,

Posted by Technology Yogi, Saturday, May 30, 2009 5:39 PM | 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, 5:10 PM | 0 comments |