-- This is the summary table for Stocks --
-- working version --

create table StockSummary
(Symbol  varchar2(10),
AvgOpenHOD_Percent  number(4,2),
AvgOpenHOD_Dollars number(5,2),
AvgOpenLOD_Percent number(4,2),
AvgSwing_Percent number(4,2),
AvgLowClose_Percent number(4,2),
AvgCloseNextHOD_Percent number(4,2),
AvgCloseNextHOD_Dollars number(5,2),
AvgVolume number,
FiveDayLOD_Entry number(5,2),
TenDayLOD_Entry number(5,2),
FiveDayCOD_Exit number(5,2),
TenDayCOD_Exit number(5,2),
OpenHOD_UpFour_Twenty number(3,1),
OpenHOD_UpFour_OneFifty number(3,1),
OpenHOD_UpThree_Twenty number(3,1),
OpenHOD_UpThree_OneFifty number(3,1),
OpenHOD_UpTwoHalf_Twenty number(3,1),
OpenHOD_UpTwoHalf_OneFifty number(3,1),
OpenHOD_UpTwoTwo_Twenty number(3,1),
OpenHOD_UpTwoTwo_OneFifty number(3,1),
CloseNext_UpFour_Twenty number(3,1),
CloseNext_UpFour_OneFifty number(3,1),
CloseNext_UpThree_Twenty number(3,1)	,
CloseNext_UpThree_OneFifty number(3,1),
CloseNext_UpTwoHalf_Twenty number(3,1),
CloseNext_UpTwoHalf_OneFifty number(3,1),
CloseNext_UpTwoTwo_Twenty number(3,1),
CloseNext_UpTwoTwo_OneFifty number(3,1),
constraint UK_Symbol unique (Symbol));


-- This is the stock table for each individdual stock or one big table
	for all stocks --
-- Working version of a Stock table --
-- Constraints still needs to be created ---

create table Stock
(Day Date,
Open number(9,2),
High number(9,2),
Low number(9,2),
Close number(9,2),
Volume number,
OpenHOD_Percent number(4,2),
OpenHOD_Dollars number(5,2),
OpenLOD_Percent number(4,2)	,
Swing_Percent number(4,2),
LowClose_Percent number(4,2)	,
CloseNextHOD_Percent number(4,2),
CloseNextHOD_Dollars number(5,2),

constraint CK_Volume check(Volume>200000));

-- This is all the functions that would be  used by the program to access
	the data and affect the information --

-- Procedure that calculates data for the ssummary table --

calculate AvgOpenHOD_Percent
calculate AvgOpenHOD_Dollars
calculate AvgOpenLOD_Percent
calculate AvgSwing_Percent
calculate AvgLowClose_Percent
calculate AvgCloseNextHOD_Percent
calculate AvgCloseNextHOD_Dollars
calculate AvgVolume
calculate FiveDayLOD_Entry
calculate TenDayLOD_Entry
calculate FiveDayCOD_Exit
calculate TenDayCOD_Exit
calculate OpenHOD_UpFour_Twenty
calculate OpenHOD_UpFour_OneFifty
calculate OpenHOD_UpThree_Twenty
calculate OpenHOD_UpThree_OneFifty
calculate OpenHOD_UpTwoHalf_Twenty
calculate OpenHOD_UpTwoHalf_OneFifty
calculate OpenHOD_UpTwoTwo_Twenty
calculate OpenHOD_UpTwoTwo_OneFifty
calculate CloseNext_UpFour_Twenty
calculate CloseNext_UpFour_OneFifty
calculate CloseNext_UpThree_Twenty
calculate CloseNext_UpThree_OneFifty
calculate CloseNext_UpTwoHalf_Twenty
calculate CloseNext_UpTwoHalf_OneFifty
calculate CloseNext_UpTwoTwo_Twenty
calculate CloseNext_UpTwoTwo_OneFifty

-- This is the package that calculates all  data for summary table --
-- This will reinsert new data --

create package Summary_Info
as
	Var_Symbol;
	Var_AvgOpenHOD_Percent;
	Var_AvgOpenHOD_Dollars;
	Var_AvgOpenLOD_Percent;
	Var_AvgSwing_Percent;
	Var_AvgLowClose_Percent;
	Var_AvgCloseNextHOD_Percent;
	Var_AvgCloseNextHOD_Dollars;
	Var_AvgVolume;
	Var_FiveDayLOD_Entry;
	Var_TenDayLOD_Entry;
	Var_FiveDayCOD_Exit;
	Var_TenDayCOD_Exit;
	Var_OpenHOD_UpFour_Twenty;
	Var_OpenHOD_UpFour_OneFifty;
	Var_OpenHOD_UpThree_Twenty;
	Var_OpenHOD_UpThree_OneFifty;
	Var_OpenHOD_UpTwoHalf_Twenty;
	Var_OpenHOD_UpTwoHalf_OneFifty;
	Var_OpenHOD_UpTwoTwo_Twenty;
	Var_OpenHOD_UpTwoTwo_OneFifty;
	Var_CloseNext_UpFour_Twenty;
	Var_CloseNext_UpFour_OneFifty;
	Var_CloseNext_UpThree_Twenty;
	Var_CloseNext_UpThree_OneFifty;
	Var_CloseNext_UpTwoHalf_Twenty;
	Var_CloseNext_UpTwoHalf_OneFifty;
	Var_CloseNext_UpTwoTwo_Twenty;
	Var_CloseNext_UpTwoTwo_OneFifty;
	CURSOR Symbol_crsr IS
		select Symbol from StockSummary;
procedure Calc_Summary_Info ()
as
begin
	
	OPEN Symbol_crsr;

	LOOP --  this sets a loop that allows the program to step through the records of the cursor -- 
		FETCH Symbol_crsr INTO Var_Symbol;
		EXIT WHEN Symbol_crsr%NOTFOUND -- stops looking when no records are found --

		select avg(Volume), avg(OpenHOD_Percent), avg(OpenHOD_Dollars), avg(OpenLOD_Percent),
			avg(Swing_Percent), avg(LowClose_Percent), avg(CloseNextHOD_Percent), avg(CloseNextHOD_Dollars)
		into Var_AvgVolume, Var_AvgOpenHOD_Percent, Var_AvgOpenHOD_Dollars, Var_AvgOpenLOD_Percent,
			Var_AvgSwing_Percent, Var_AvgLowClose_Percent, Var_AvgCloseNextHOD_Percent, 
			Var_AvgCloseNextHOD_Dollars
		from Var_Symbol;

		-- execute all calculations for the Summary table -- 
		execute Summary_Info.Calc_FiveDayLOD_Entry ()
		

		-- now we want to update the stock in the summary table with all its summary info --
		

end Calc_Summary_Info;

-- Need calculations for summary functions  --

function Calc_FiveDayLOD_Entry ()

end Calc_FiveDayLOD_Entry;

function Calc_TenDayLOD_Entry ()

end Calc_TenDayLOD_Entry;

function Calc_FiveDayCOD_Exit ()

end Calc_FiveDayCOD_Exit;

function Calc_TenDayCOD_Exit ()

end Calc_TenDayCOD_Exit;

function Calc_OpenHOD_UpFour_Twenty ()

end Calc_OpenHOD_UpFour_Twenty;

function Calc_OpenHOD_UpFour_OneFifty ()

end Calc_OpenHOD_UpFour_OneFifty;p

function Calc_OpenHOD_UpThree_Twenty ()

end Calc_OpenHOD_UpThree_Twenty;

function Calc_OpenHOD_UpThree_OneFifty ()

end Calc_OpenHOD_UpThree_OneFifty;

function Calc_OpenHOD_UpTwoHalf_Twenty ()

end Calc_OpenHOD_UpTwoHalf_Twenty;

function Calc_OpenHOD_UpTwoHalf_OneFifty ()

end Calc_OpenHOD_UpTwoHalf_OneFifty;

function Calc_OpenHOD_UpTwoTwo_Twenty ()

end Calc_OpenHOD_UpTwoTwo_Twenty;

function Calc_OpenHOD_UpTwoTwo_OneFifty ()

end Calc_OpenHOD_UpTwoTwo_OneFifty;


-- End of package to calculate all data forr summary table --

-- Trigger that calculates data for the inddividual stock tables --
-- This is a row-level trigger and executess once for each row affected --
-- This is the trigger to update data -- --- Need to build trigger for first time stuff --

calculate OpenHOD_Percent
calculate OpenHOD_Dollars
calculate OpenLOD_Percent
calculate Swing_Percent
calculate LowClose_Percent
calculate CloseNextHOD_Percent
calculate CloseNextHOD_Dollars

-- Syntax for trigger -- pg515 in Oracle 9ii and pg214 Oracle 7i

create trigger Stock_Info_INSRT_Row
for each row
begin
	-- call package for calculations --
	execute Stock_Info.Calc_Stock_Info ();
End

-- End of syntax for trigger --

-- End of trigger --

-- This is the package of functions for alll calculations --
-- This assumes that we already have all thhe data contained in the stock tables -- 

create package Stock_Info
as
	Var_Day;
	Var_Open;
	Var_High;
	Var_Low;
	Var_Close;
	Var_OpenHOD_Percent;
	Var_OpenHOD_Dollars;
	Var_OpenLOD_Percent;
	Var_Swing_Percent;
	Var_LowClose_Percent;
	Var_CloseNextHOD_Percent;
	Var_CloseNextHOD_Dollars;
procedure Calc_Stock_Info ()
as
begin
	select Day, Open, High, Low, Close
	into Var_Day, Var_Open, Var_High, Var_Low, Var_Close
	from Stock;

	-- execute calculations --
	execute Stock_Info.Calc_OpenHOD_Percent (Var_Open, Var_High);
	execute Stock_Info.Calc_OpenHOD_Dollars (Var_Open, Var_High);
	execute Stock_Info.Calc_OpenLOD_Percent (Var_Open, Var_Low);
	execute Stock_Info.Calc_Swing_Percent (Var_High, Var_Low);
	execute Stock_Info.Calc_LowClose_Percent (Var_Close, Var_Low);
	execute Stock_Info.Calc_CloseNextHOD_Percent (Var_High, Var_Close);
	execute Stock_Info.Calc_CloseNextHOD_Dollars (Var_High, Var_Close);

	-- Now Update calculation data into stock table --
	update Stock(OpenHOD_Percent, OpenHOD_Dollars, OpenLOD_Percent, Swing_Percent,
			LowClose_Percent, CloseNextHOD_Percent, CloseNextHOD_Dollars)
	set OpenHOD_Percent = Var_OpenHOD_Percent, OpenHOD_Dollars = Var_OpenHOD_Dollars,
			OpenLOD_Percent = Var_OpenLOD_Percent, Swing_Percent = Var_Swing_Percent,
			LowClose_Percent = Var_LowClose_Percent, CloseNextHOD_Percent = Var_CloseNextHOD_Percent,
			CloseNextHOD_Dollars = Var_CloseNextHOD_Dollars
	where Day = Var_Day;

end Calc_Stock_Info;

function Calc_OpenHOD_Percent (Var_Open in number, Var_High in number)
	return Number
	is
	Var_OpenHOD_Percent Number(4,2);
begin
	Var_OpenHOD_Percent := (Var_High - Var_Open) / Var_Open;
	return (Var_OpenHOD_Percent);

end Calc_OpenHOD_Percent;

function Calc_OpenHOD_Dollars (Var_Open in number, Var_High in number)
	return Number
	is
	Var_OpenHOD_Dollars Number(5,2);
begin
	Var_OpenHOD_Dollars := (Var_High - Var_Open);
	return (Var_OpenHOD_Dollars);

end Calc_OpenHOD_Dollars;

function Calc_OpenLOD_Percent (Var_Open in number, Var_Low in number)
	return Number
	is
	Var_OpenLOD_Percent Number(4,2);
begin
	Var_OpenLOD_Percent := (Var_Open - Var_Low) / Var_Open;
	return (Var_OpenLOD_Percent);

end Calc_OpenLOD_Percent;

function Calc_Swing_Percent (Var_High in number, Var_Low in number)
	return Number
	is
	Var_Swing_Percent Number(4,2);
begin
	Var_Swing_Percent := (Var_High - Var_Low) / Var_Low;
	return (Var_Swing_Percent);

end Calc_Swing_Percent;

function Calc_LowClose_Percent (Var_Close in number, Var_Low in number)
	return Number
	is
	Var_LowClose_Percent Number(4,2);
begin
	Var_LowClose_Percent := (Var_Close - Var_Low) / Var_Low;
	return (Var_LowClose_Percent);

end Calc_LowClose_Percent;

function Calc_CloseNextHOD_Percent (Var_High in number, Var_Close in number)
	return Number
	is
	Var_CloseNextHOD_Percent Number(4,2);
begin
	Var_CloseNextHOD_Percent := (Var_High - (Var_Close + 1)) / (Var_Close + 1);
	return (Var_CloseNextHOD_Percent);

end Calc_CloseNextHOD_Percent;

function Calc_CloseNextHOD_Dollars (Var_High in number, Var_Close in number)
	return Number
	is
	Var_CloseNextHOD_Dollars Number(5,2);
begin
	Var_CloseNextHOD_Dollars := (Var_High - Var_Close);
	return (Var_CloseNextHOD_Dollars);

end Calc_CloseNextHOD_Dollars;

end Stock_Info;

-- End of package --


-- Functions in Fritz program --

Update Symbol list	(once weekly) -- truncate table and insert only stock names
Update Symbol Information	(once daily) -- summary calculations
	-- For this function what we want to do is insert each stocks data into each individual table
		which will run the trigger for updating the data --
	-- Then we want to run the actual updating of the Summary tables info --

-- End of Functions in program --

-- This is the basic program of the update  symbol list --

truncate StockSummary;	-- This will clear the symbol list for all new symbols -- 
search for stock name
if exists
	-- open and update stock table with new day and delete last row in table --
	insert into StockSummary (Symbol)
	values (stock);
else
	-- create new table for stock and create insert trigger for table --
	-- run script at the top of this document to create the table and the trigger -- 
	-- will be inserting all new data at the time that the UPDATE SYMBOL INFO is run -- 
end if
next stock

GENERAL COMMENTS
-- adding a new row will engage insert triggger to calculate stock info --
-- run package to recalculate new summary iinfo --
-- run package to calculate summary info ---
-- this is the code that should be run befoore actually UPDATE SYMBOL LIST is run -- 
-- this will determine which stock stuff wee actually want to keep --
	if (select avg(Volume) from Stock) < 200000 then
		drop insert trigger
		drop table Stock
	else
		-- create update trigger for stock since we are saving it --
	end if
--  WHEN EVERYTHING IS SAID AND DONE THEN WWHAT YOU NEED TO DO IS START TO LOOK AT
	USING AND CREATING EXCEPTIONS TO CONTROL THE OUTCOME OF SELECTING AND MANIPULATING DATA -- 
-- REFER TO PAGE 215 IN ORACLE 7I