How to calculate the ADX

Question

How to calculate the ADX

sushilb

SSC Eights!

Points: 856
More actions
October 17, 2011 at 1:10 pm

#244737

Hi All,
i am trying to calculate the ADX as per attached excel , but succeded half way , can u suggest me to proceed further
-------------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER proc [dbo].[ADX]
as
set
nocount on
Declare
@Cnt int,
@crt int,
@newcnt int
IF
OBJECT_ID(N'tempdb..#Adxdata', N'U') IS NOT NULL
drop
table #Adxdata;
CREATE
TABLE #Adxdata (ID INT NOT NULL, Symbol_Code VARCHAR(50), Transaction_date DATEtime,High_Price Decimal(18,2),Low_Price Decimal(18,2), Close_Price Decimal(18,2))
set
@Cnt=(select count(*) from EOD_NSE_Stock where
Transaction_date
between dateadd(day, -28, getdate()) and getdate() and Symbol_Code='Wipro' )
IF
OBJECT_ID(N'tempdb..#ADX_Data', N'U') IS NOT NULL
drop
table #ADX_Data;
IF
OBJECT_ID(N'tempdb..#ADX_Data1', N'U') IS NOT NULL
drop
table #ADX_Data1;
if
(@Cnt<28)
begin
set
@crt=28-@Cnt
set
@newcnt=@crt+28
--print @newcnt
--print @crt
--print @Cnt
INSERT
INTO #Adxdata (ID, Symbol_Code, Transaction_date,High_Price,Low_Price, Close_Price)
select
Transaction_Id,Symbol_Code, Transaction_date,High_Price,Low_Price, Close_Price from EOD_NSE_Stock where
Transaction_date
between dateadd(day, -@newcnt, getdate()) and getdate()
and
Symbol_Code='Wipro'
;
WITH OrderedData AS (
SELECT
seq = ROW_NUMBER() OVER(PARTITION BY Symbol_Code ORDER BY Transaction_date),
ID,
Symbol_Code
,
Transaction_date
,
High_Price
,
Low_Price
,
Close_Price
FROM
#Adxdata
where
Transaction_date between dateadd(day, -@newcnt, getdate()) and getdate())
SELECT
d1.ID,
d1.Symbol_Code,
d1.Transaction_date,
d1.High_Price,
d1.Low_Price ,
d1.Close_Price,
d1.seq,
d2.seq as d2seq,
dbo.Greatest((d2.High_Price-d2.Low_Price),abs(d2.High_Price-d1.Close_Price),abs(d2.Low_Price-d1.Close_Price))as TR
,
case when ((d2.High_Price-d1.High_Price)>(d1.Low_Price-d1.Low_Price))
then
dbo.Greatest((d2.High_Price-d1.High_Price),0,0)
when ((d2.High_Price-d1.High_Price)<(d1.Low_Price-d1.Low_Price))
then
0
end
as Positive_DM ,
case when ((d1.Low_Price-d1.Low_Price)>(d2.High_Price-d1.High_Price))
then
dbo.Greatest((d1.Low_Price-d1.Low_Price),0,0)
when ((d1.Low_Price-d1.Low_Price)<(d2.High_Price-d1.High_Price))
then
0
end
as Negative_DM
into
#ADX_Data
FROM
OrderedData d1
LEFT
JOIN OrderedData d2 ON d2.Symbol_Code = d1.Symbol_Code
AND d1.seq+1 = d2.seq
and d1.Transaction_date between dateadd(day, -@newcnt, getdate()) and getdate()
select
* from #ADX_Data
Select sum(TR) as TR14, sum(Positive_DM) AS PDM14,sum(Negative_DM) as NDM14,(100*(sum(Positive_DM)/(sum(TR)))) as PDI14,
(100*(sum(Negative_DM)/(sum(TR)))) as NDI14,
abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR))))) as DI14diff,
((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR))))) as DI14Sum,
100*(abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR)))))/((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR)))))) as DX
from #ADX_Data
end
else if (@Cnt=28)
begin
print @Cnt
INSERT INTO #Adxdata (ID, Symbol_Code, Transaction_date,High_Price,Low_Price, Close_Price)
select
Transaction_Id,Symbol_Code, Transaction_date,High_Price,Low_Price, Close_Price from EOD_NSE_Stock where
Transaction_date
between dateadd(day, -28, getdate()) and getdate()
and
Symbol_Code='Wipro'
;
WITH OrderedData AS (
SELECT
seq = ROW_NUMBER() OVER(PARTITION BY Symbol_Code ORDER BY Transaction_date),
ID,Symbol_Code
,Transaction_date
,High_Price
,Low_Price
,Close_Price
FROM
#Adxdata
where
Transaction_date between dateadd(day, -28, getdate()) and getdate())
SELECT
d1.ID,
d1.Symbol_Code,
d1.Transaction_date,
d1.High_Price,
d1.Low_Price ,
d1.Close_Price,
d1.seq,
d2.seq as d2seq,
dbo.Greatest((d2.High_Price-d2.Low_Price),abs(d2.High_Price-d1.Close_Price),abs(d2.Low_Price-d1.Close_Price))as TR
,
case when ((d2.High_Price-d1.High_Price)>(d1.Low_Price-d1.Low_Price))
then
dbo.Greatest((d2.High_Price-d1.High_Price),0,0)
when ((d2.High_Price-d1.High_Price)<(d1.Low_Price-d1.Low_Price))
then
0
end
as Positive_DM ,
case when ((d1.Low_Price-d1.Low_Price)>(d2.High_Price-d1.High_Price))
then
dbo.Greatest((d1.Low_Price-d1.Low_Price),0,0)
when ((d1.Low_Price-d1.Low_Price)<(d2.High_Price-d1.High_Price))
then
0
end
as Negative_DM
into
#ADX_Data1
FROM
OrderedData d1
LEFT
JOIN OrderedData d2 ON d2.Symbol_Code = d1.Symbol_Code
AND d1.seq+1 = d2.seq
and d1.Transaction_date between dateadd(day, -28, getdate()) and getdate()
select
* from #ADX_Data1
;With AdxCalculation
as
(
SELECT
seq = ROW_NUMBER() OVER(PARTITION BY Symbol_Code ORDER BY Transaction_date),
ID,Symbol_Code
,Transaction_date
,High_Price
,Low_Price
,Close_Price
FROM
Select sum(TR) as TR14, sum(Positive_DM) AS PDM14,sum(Negative_DM) as NDM14,(100*(sum(Positive_DM)/(sum(TR)))) as PDI14,
(100*(sum(Negative_DM)/(sum(TR)))) as NDI14,
abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR))))) as DI14diff,
((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR))))) as DI14Sum,
100*(abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR)))))/((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR)))))) as DX
from #ADX_Data1
where d1.seq+13=d2.seq
end
--adx

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286589 More actions · Answer 1

About the only I can suggest to get you started is to read the first link in my signature. We can't begin to parse this as it was posted. The only table it appears is a temp table but there is no sample data. And there is nothing even remotely attempting to explain what you are trying to do.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Sean Lange SSC Guru Points: 286589 More actions · Answer 2

You really should post to the forum instead of PM.

However, the excel file contains data but I don't want to have to create a table and try to figure out your data types. Even with the data you posted I am not really sure what you are trying to do.

Again, please read the first link in my signature. Then put your self in the shoes of somebody trying to help you and ask yourself if you would have enough information to help. As it is now I could spend a couple hours of my time to setup your problem, or you could spend time up front so that my time is spent working on your problem. You just have to keep in mind that all the time from everybody on here is volunteer. We do this because we enjoy helping and sharing knowledge. If you were paying us it would be a whole different story.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sushilb SSC Eights! Points: 856 More actions · Answer 3

Ok ,

Thanks for your help.

Wasted your valuable time.

Sean Lange SSC Guru Points: 286589 More actions · Answer 4

You certainly didn't waste my time. I am willing to help I just need some details about what you are trying to do. Make it easy for me (or somebody else) to help by posting the ddl, sample data (insert statements) and desired output based on your sample data. In return you will get tested code that will help you solve your problem.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sushilb SSC Eights! Points: 856 More actions · Answer 5

I just trying to calculate ADX index as per attached sheet , right

the Adx is calcuated based on 27 days high , low and close data of company,

the formula for caulculating TR,+DM 1,-DM 1,TR14,+DM14,-DM14

,+DI14,-DI14,DI 14 Diff,DI 14 Sum, DX,ADX is mention in attached excel cell .

the attached here is the excel which has the all the formula

for caulculating TR,+DM 1,-DM 1,TR14,+DM14,-DM14

,+DI14,-DI14,DI 14 Diff,DI 14 Sum, DX,ADX

i could be able to calculate the TR,+DM 1,-DM 1

but not able to caculate the further

such as TR14,+DM14,-DM14 ,+DI14,-DI14,DI 14 Diff,DI 14 Sum, DX,ADX.

Sean Lange SSC Guru Points: 286589 More actions · Answer 6

OK put yourself in my shoes. Do you think you have provided enough information for me to be able to help? I have asked for ddl, sample data (insert statements) and desired output based on the sample data. In this case it would probably be best to include the formulas to get these values. I don't want to dig through excel spreadsheets and still have to guess as to what the sql might be. Your first post had a number of tables. If you were paying me I would be happy to spend a couple days unraveling and assembling your requirements. However, since this type of analysis is not really fun I don't want to do this for free. If you can post ddl, sample data (insert statements) and desired output based on the sample data (read the first link in my signature for how to assemble this information), I will be happy to help.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sushilb SSC Eights! Points: 856 More actions · Answer 7

Sample data to test

----create the table NSE_Stock_Data run below query to get the data

------------------------------------------------------------------

--drop table NSE_Stock_Data

create table NSE_Stock_Data

([Transaction_Id] [int] NULL,

[Transaction_date] [datetime] NULL,

[Symbol_Code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Symbol_Name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Strike_Price] [money] NULL,

[Option_Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Series_Code] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[Expire_Date] [datetime] NULL,

[Open_Price] [money] NULL,

[High_Price] [money] NULL,

[Low_Price] [money] NULL,

[Close_Price] [money] NULL,

[Last_Price] [money] NULL,

[Previous_Price] [money] NULL,

[Total_Traded_Qty] [money] NULL,

[Total_Traded_Value] [money] NULL,

[52_Wk_High] [money] NULL,

[52_Wk_Low] [money] NULL,

[Number_of_Trade] [money] NULL,

[Underthe] [varchar](50) NULL,

[WAP] [money] NULL,

[Notional_Value] [money] NULL,

[PR_Value] [money] NULL,

[Record_Entry_Date] [datetime] NULL)

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values(

'82605','9/2/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'19.77','19.77','1.58','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82606','9/26/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'19.77','30.45','29.96','30.1',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82607','9/27/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'19.77','29.35','28.74','28.9',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

(

'82608','9/28/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'19.77','29.35','28.56','28.92',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82609','9/29/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'19.77','29.29','28.41','28.48',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82610','9/30/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'19.77','19.77','18.58','18.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82619','10/9/2011 0:00','VIX','VIX',NULL,NULL,NULL,NULL,'28.67','27.66','28.47','38.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82620','9/25/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'28.85','27.83','28.28','30.24',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82621','9/24/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'28.64','27.4','27.49','29.87',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82622','9/23/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.68','27.09','27.23','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82623','9/22/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82632','10/1/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'30.2','44.53','43.98','44.52',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82633','10/2/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'30.28','44.93','44.36','44.65',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82634','10/4/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'30.45','45.39','44.7','45.22',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82635','10/5/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'29.35','45.7','45.13','45.45',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82636','10/6/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'29.35','45.63','44.89','45.49',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82637','10/7/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'29.29','45.52','44.2','44.24',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82638','10/8/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'28.83','44.71','44','44.62',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82639','10/9/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'28.73','45.15','43.76','45.15',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82640','10/10/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'26.87','45.65','44.46','44.54',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82641','10/11/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.41','45.87','45.13','45.66',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82642','10/12/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.41','45.99','45.27','45.95',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82643','10/13/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.41','46.35','45.8','46.33',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82644','10/14/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.41','46.61','46.1','46.31',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82645','10/15/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.41','46.47','45.77','45.94',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82646','10/16/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.41','46.3','45.14','45.6',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82647','9/21/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82648','9/20/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82649','9/19/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data

(

Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)

Values

('82650','9/18/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82651','10/17/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82652','10/18/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82653','10/19/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82654','10/20/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82655','10/21/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82656','10/22/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82657','10/23/2011 0:00','Wipro','Wipro',NULL,NULL,NULL,NULL,'27.21','26.18','26.35','5.78',NULL,'19.77','0','0','29.05','14.35','0',NULL,NULL,NULL,NULL,'8/22/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82664','10/24/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'27.33','26.68','26.36','5.79',NULL,'19.78','0','0','29.06','14.36','0',NULL,NULL,NULL,NULL,'8/23/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82665','10/25/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'27.45','26.69','26.37','5.8',NULL,'19.8','0','0','29.07','14.37','0',NULL,NULL,NULL,NULL,'8/24/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82666','10/26/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'27.57','26.7','26.38','5.81',NULL,'19.8133','0','0','29.08','14.38','0',NULL,NULL,NULL,NULL,'8/25/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82667','10/27/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'27.69','26.71','26.39','5.82',NULL,'19.8283','0','0','29.09','14.39','0',NULL,NULL,NULL,NULL,'8/26/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82668','10/28/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'27.81','26.72','26.4','5.83',NULL,'19.8433','0','0','29.1','14.4','0',NULL,NULL,NULL,NULL,'8/27/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82669','10/29/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'27.93','26.73','26.41','5.84',NULL,'19.8583','0','0','29.11','14.41','0',NULL,NULL,NULL,NULL,'8/28/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82670','10/30/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'28.05','26.74','26.42','5.85',NULL,'19.8733','0','0','29.12','14.42','0',NULL,NULL,NULL,NULL,'8/29/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82671','10/31/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'28.17','26.75','26.43','5.86',NULL,'19.8883','0','0','29.13','14.43','0',NULL,NULL,NULL,NULL,'8/30/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82672','11/1/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'28.29','26.76','26.44','5.87',NULL,'19.9033','0','0','29.14','14.44','0',NULL,NULL,NULL,NULL,'8/31/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82673','11/2/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'28.41','26.77','26.45','5.88',NULL,'19.9183','0','0','29.15','14.45','0',NULL,NULL,NULL,NULL,'9/1/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82674','11/3/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'28.53','26.78','26.46','5.89',NULL,'19.9333','0','0','29.16','14.46','0',NULL,NULL,NULL,NULL,'9/2/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82675','11/4/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'28.65','26.79','26.47','4.9',NULL,'19.9483','0','0','29.17','14.47','0',NULL,NULL,NULL,NULL,'9/3/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82676','11/5/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'28.77','26.8','26.48','9.91',NULL,'19.9633','0','0','29.18','14.48','0',NULL,NULL,NULL,NULL,'9/4/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82677','11/6/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'28.89','26.81','26.49','5.92',NULL,'19.9783','0','0','29.19','14.49','0',NULL,NULL,NULL,NULL,'9/5/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82678','11/7/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'29.01','26.82','26.5','6.93',NULL,'19.9933','0','0','29.2','14.5','0',NULL,NULL,NULL,NULL,'9/6/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82679','11/8/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'29.13','26.83','26.51','7.94',NULL,'20.0083','0','0','29.21','14.51','0',NULL,NULL,NULL,NULL,'9/7/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82680','11/9/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'29.25','26.84','26.52','9.95',NULL,'20.0233','0','0','29.22','14.52','0',NULL,NULL,NULL,NULL,'9/8/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82682','11/11/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'29.49','26.86','26.54','6.97',NULL,'20.0533','0','0','29.24','14.54','0',NULL,NULL,NULL,NULL,'9/10/2011 15:59')

Insert Into NSE_Stock_Data ( Transaction_Id,Transaction_date, Symbol_Code, Symbol_Name, Strike_Price, Option_Type, Series_Code, Expire_Date, Open_Price, High_Price, Low_Price, Close_Price, Last_Price, Previous_Price, Total_Traded_Qty, Total_Traded_Value, [52_Wk_High], [52_Wk_Low], Number_of_Trade, Underthe, WAP, Notional_Value, PR_Value, Record_Entry_Date)Values('82683','11/12/2011 0:00','Wipro','Wipro',NULL,'','',NULL,'29.61','26.87','26.55','5.98',NULL,'20.0683','0','0','29.25','14.55','0',NULL,NULL,NULL,NULL,'9/11/2011 15:59')

---------------------------------------------------------------------

----Now run the below query will get the Output as below and i have mention comments where i am in trouble

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

-- adx

ALTER proc [dbo].[ADX]

as

set

nocount on

Declare

@Cnt int,

@crt int,

@newcnt int

IF

OBJECT_ID(N'tempdb..#Adxdata', N'U') IS NOT NULL

drop

table #Adxdata;

CREATE

TABLE #Adxdata (ID INT NOT NULL, Symbol_Code VARCHAR(50), Transaction_date DATEtime,High_Price Decimal(18,2),Low_Price Decimal(18,2), Close_Price Decimal(18,2))

set

@Cnt=(select count(*) from EOD_NSE_Stock where

Transaction_date

between dateadd(day, -28, getdate()) and getdate() and Symbol_Code='Wipro' )

IF

OBJECT_ID(N'tempdb..#ADX_Data', N'U') IS NOT NULL

drop

table #ADX_Data;

IF

OBJECT_ID(N'tempdb..#ADX_Data1', N'U') IS NOT NULL

drop

table #ADX_Data1;

if --if rows are less than 28 i.e 28 days data

(@Cnt<28)

begin

set

@crt=28-@Cnt

set

@newcnt=@crt+28 ---to get exact 28 rows i.e 28 days data

--print @newcnt

--print @crt

--print @Cnt

---Insert data into temporary table

INSERT

INTO #Adxdata (ID, Symbol_Code, Transaction_date,High_Price,Low_Price, Close_Price)

select

Transaction_Id,Symbol_Code, Transaction_date,High_Price,Low_Price, Close_Price from EOD_NSE_Stock where

Transaction_date

between dateadd(day, -@newcnt, getdate()) and getdate()

and

Symbol_Code='Wipro'

;

WITH OrderedData AS (

SELECT

seq = ROW_NUMBER() OVER(PARTITION BY Symbol_Code ORDER BY Transaction_date),

ID,

Symbol_Code

,

Transaction_date

,

High_Price

,

Low_Price

,

Close_Price

FROM

#Adxdata

where

Transaction_date between dateadd(day, -@newcnt, getdate()) and getdate())

SELECT

d1.ID,

d1.Symbol_Code,

d1.Transaction_date,

d1.High_Price,

d1.Low_Price ,

d1.Close_Price,

d1.seq as d1,

d1.seq as d2,

--Greatest function returns the highest value out of three values

dbo.Greatest((d2.High_Price-d2.Low_Price),abs(d2.High_Price-d1.Close_Price),abs(d2.Low_Price-d1.Close_Price))as TR

,

--Postive DM

case when ((d2.High_Price-d1.High_Price)>(d1.Low_Price-d1.Low_Price))

then

dbo.Greatest((d2.High_Price-d1.High_Price),0,0)

when ((d2.High_Price-d1.High_Price)<(d1.Low_Price-d1.Low_Price))

then

0

end

as Positive_DM ,

--Negative DM

case when ((d1.Low_Price-d1.Low_Price)>(d2.High_Price-d1.High_Price))

then

dbo.Greatest((d1.Low_Price-d1.Low_Price),0,0)

when ((d1.Low_Price-d1.Low_Price)<(d2.High_Price-d1.High_Price))

then

0

end

as Negative_DM

into

#ADX_Data

FROM

OrderedData d1

LEFT

JOIN OrderedData d2 ON d2.Symbol_Code = d1.Symbol_Code

AND d1.seq+1 = d2.seq

and d1.Transaction_date between dateadd(day, -@newcnt, getdate()) and getdate()

--select

--* from #ADX_Data

begin try

---Below Here i am not able to calculate the the PDI14,NDI14,DI14diff,DI14Sum,DX as shown in excel

select Transaction_date, sum(TR) as TR14,

sum(Positive_DM) AS PDM14,

sum(Negative_DM) as NDM14,

case when (sum(TR)>0) then(100*(sum(Positive_DM)/(sum(TR))))

when (sum(TR)=0) then 0 end

as PDI14,

case when (sum(TR)>0) then(100*(sum(Negative_DM)/(sum(TR))))

when (sum(TR)=0) then 0 end

as NDI14,

case when (sum(TR)>0) then abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR)))))

when (sum(TR)=0) then 0 end

as DI14diff,

case when (sum(TR)>0) then((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR)))))

when (sum(TR)=0) then 0 end

as DI14Sum,

case when (sum(TR)>0) then 100*(abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR)))))/((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR))))))

when (sum(TR)=0) then 0 end

as DX

from #ADX_Data

--where d1.seq+13=d2.seq

--group by Transaction_date

end try

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

end

else if (@Cnt=28) --if exact 28 rows i.e 28 days data then proceed for above same calculation

begin

--print @Cnt

INSERT INTO #Adxdata (ID, Symbol_Code, Transaction_date,High_Price,Low_Price, Close_Price)

select

Transaction_Id,Symbol_Code, Transaction_date,High_Price,Low_Price, Close_Price from EOD_NSE_Stock where

Transaction_date

between dateadd(day, -28, getdate()) and getdate()

and

Symbol_Code='Wipro'

;

WITH OrderedData AS (

SELECT

seq = ROW_NUMBER() OVER(PARTITION BY Symbol_Code ORDER BY Transaction_date),

ID,Symbol_Code

,Transaction_date

,High_Price

,Low_Price

,Close_Price

FROM

#Adxdata

where

Transaction_date between dateadd(day, -28, getdate()) and getdate())

SELECT

d1.ID,

d1.Symbol_Code,

d1.Transaction_date,

d1.High_Price,

d1.Low_Price ,

d1.Close_Price,

d1.seq as d1,

d1.seq as d2,

dbo.Greatest((d2.High_Price-d2.Low_Price),abs(d2.High_Price-d1.Close_Price),abs(d2.Low_Price-d1.Close_Price))as TR

,

case when ((d2.High_Price-d1.High_Price)>(d1.Low_Price-d1.Low_Price))

then

dbo.Greatest((d2.High_Price-d1.High_Price),0,0)

when ((d2.High_Price-d1.High_Price)<(d1.Low_Price-d1.Low_Price))

then

0

end

as Positive_DM ,

case when ((d1.Low_Price-d1.Low_Price)>(d2.High_Price-d1.High_Price))

then

dbo.Greatest((d1.Low_Price-d1.Low_Price),0,0)

when ((d1.Low_Price-d1.Low_Price)<(d2.High_Price-d1.High_Price))

then

0

end

as Negative_DM

into

#ADX_Data1

FROM

OrderedData d1

LEFT

JOIN OrderedData d2 ON d2.Symbol_Code = d1.Symbol_Code

AND d1.seq+1 = d2.seq

and d1.Transaction_date between dateadd(day, -28, getdate()) and getdate()

--select

--* from #ADX_Data1

begin try

---Below Here i am not able to calculate the the PDI14,NDI14,DI14diff,DI14Sum,DX as shown in excel

Select

Transaction_date, sum(TR) as TR14,

sum(Positive_DM) AS PDM14,

sum(Negative_DM) as NDM14,

case when (sum(TR)>0) then(100*(sum(Positive_DM)/(sum(TR))))

when (sum(TR)=0) then 0 end

as PDI14,

case when (sum(TR)>0) then(100*(sum(Negative_DM)/(sum(TR))))

when (sum(TR)=0) then 0 end

as NDI14,

case when (sum(TR)>0) then abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR)))))

when (sum(TR)=0) then 0 end

as DI14diff,

case when (sum(TR)>0) then((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR)))))

when (sum(TR)=0) then 0 end

as DI14Sum,

case when (sum(TR)>0) then 100*(abs((100*(sum(Positive_DM)/(sum(TR))))-(100*(sum(Negative_DM)/(sum(TR)))))/((100*(sum(Positive_DM)/(sum(TR))))+(100*(sum(Negative_DM)/(sum(TR))))))

when (sum(TR)=0) then 0 end

as DX

from #ADX_Data1

where d1.seq+13=d2.seq

end try

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber

,ERROR_SEVERITY() AS ErrorSeverity

,ERROR_STATE() AS ErrorState

,ERROR_PROCEDURE() AS ErrorProcedure

,ERROR_LINE() AS ErrorLine

,ERROR_MESSAGE() AS ErrorMessage;

END CATCH

end

--

sushilb SSC Eights! Points: 856 More actions · Answer 8

i am expecting an output to be as show in excel

High LowClose TR+DM1 -DM1 TR14+DM14 -DM14 +DI14 -DI14 DI14Diff DI14SumDX ADX

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 9

sushilb (11/12/2011)
Sample data to test
----create the table NSE_Stock_Data run below query to get the data
------------------------------------------------------------------
--drop table NSE_Stock_Data
create table NSE_Stock_Data
..............................................................--

Hi...by ADX can I assume you are talking about "Average Directional Index" and stock markets?

also...please could you validate the set up scripts you posted...I tried cut n paste into SSMS and ran into errors...???

regards

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

sushilb SSC Eights! Points: 856 More actions · Answer 10

Yes Sir

It is ADX - Average Directional Index (ADX).

Yes it has error. now i have resolved the error.

Just run the insert query to insert data into table.

then run the other query to get the desired output

--drop table NSE_Stock_Data1