SQL Function for On Balance Volume (Financial Query)
- by CraigJSte
I would like to create a function for On Balance Volume (SQL Function).
This is too complex of a calculation for met to figure out but here is the outline of the User Defined Table Function. If someone could help me to fill in the blanks I would appreciate it.
Craig
CREATE FUNCTION [dbo].[GetStdDev3] (@TKR VARCHAR(10))
RETURNS @results TABLE (
dayno SMALLINT IDENTITY(1,1) PRIMARY KEY
, [date] DATETIME
, [obv] FLOAT
)
AS BEGIN
DECLARE @rowcount SMALLINT
INSERT @results ([date], [obv])
// CREATE A FUNCTION FOR ON BALANCE VOLUME
// On Balance Volume is the Summ of Volume for Total Periods
// OBV = 1000 at Period = 0
// OBV = OBV Previous + Previous Volume if Close Previous Close
// OBV = OBV Previous - Previous Volume if Close < Previous Close
// OBV = OBV Previous if Close = Previous Close
// The actual Value of OBV is not important so to keep the ratio low we reduce the
// Total Value of Tickers by 1/10th or 1/100th
// For Value of Volume = Volume * .01 if Volume < 999
// For Value of Volume = Volume * .001 If Volume = 999
FROM Tickers
RETURN
END
This is the Tickers table
[dbo].[Tickers](
[ticker] [varchar](10) NULL,
[date] [datetime] NULL,
[high] [float] NULL,
[low] [float] NULL,
[open] [float] NULL,
[close] [float] NULL,
[volume] [float] NULL,
[time] [datetime] NULL,
[change] [float] NULL
)