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
