Preethi Jain Preethi Jain - 7 months ago 15
SQL Question

How to Calculate Change in Stock percentage Change Value

I am maintaining Stock Prices in my Database and this is my database structure

CREATE TABLE `sector_wise` (
`symbol_name` varchar(50) DEFAULT NULL,
`sector_name` varchar(50) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;

CREATE TABLE `historical_data` (
`symbol_name` varchar(70) DEFAULT NULL,
`current_day` date DEFAULT NULL,
`open_val` decimal(15,2) DEFAULT NULL,
`high_val` decimal(15,2) DEFAULT NULL,
`low_val` decimal(15,2) DEFAULT NULL,
`close_val` decimal(15,2) DEFAULT NULL,
`last_val` decimal(15,2) DEFAULT NULL,
`prevclose_val` decimal(15,2) DEFAULT NULL,
`volume` varchar(30) DEFAULT 'NA'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;


Insert Scripts :

Insert INTO sector_wise ('symbol_name','sector_name') VALUES ('VISESHINFO','Agriculture');



Insert INTO historical_data ('symbol_name','current_day','open_val','high_val','low_val','close_val','last_val','prevclose_val','volume') VALUES ('VISESHINFO','2016-05-03',0.05,0.10,0.05,0.05,0.05,0.10,45814.95);


This is the query i tried

SELECT sw.symbol_name AS symbol_name,
hd.current_day AS curr_day,
hd.open_val AS open_value,
hd.high_val AS high_value,
hd.low_val AS low_val,
hd.close_val AS close_val,
hd.prevclose_val AS prevclose_val,
(close_val-prevclose_val)/close_val*100 AS per_change
FROM sector_wise sw,
historical_data hd
WHERE sw.symbol_name = hd.symbol_name
AND sw.sector_name = 'Agriculture'
AND hd.current_day =
(SELECT max(current_day)
FROM historical_data)
ORDER BY per_change;


The Result i got after executing the above query is which is actually wrong , the percentage chnage should be -50.00 and not -100.00

symbol_name curr_day open_value high_value low_value close_val prevclose_val percentage_change
VISESHINFO 2016-05-03 0.05 0.10 0.05 0.05 0.10 -100.000000


Could you please tell me where exactly the mistake is in calculating the percentage change

Answer

(close_val-prevclose_val)/close_val*100 would translate to below:

((0.05-0.10)/0.05))*100 

which is equal to -100. Are you sure of the formula? If it is for price variation, shouldn't the prevclose_val be used in the formula as the denominator?