Solved

# Price Simulation in stored procedure

Posted on 2004-09-20
329 Views
I would like to create a price-simulation for 12'000 securities. At the end, it will be very complex. But I am new to SQL so I would like to start step by step. Right now, I am wondering how a code can look like for a price-simulation for each of the 12'000 'Stocks' at once where the 'price' from the table 'WBI' has to reach the level of 'sector-prize' from the table 'WBI' in steps of 0,1% and then freeze the last price before reaching 'sector-prize' so I can use the freezed price in the next procedure. The 'price' can be above or below 'sector-prize' so it has to go both ways.

Thx
ColumA

PS: I know that described as above, I only need to take 'sector-prize' less 0,1% makes the freezed-price. But as this is a first step which I have to change further, I am looking for a loop that takes me the 'price' in 0,1% steps to 'sector-prize'
0
Question by:ColumA
• 15
• 12
• 2

LVL 26

Expert Comment

ID: 12102178
Hi columA,

I'm failing to catch what you need to achieve for the moment

Could you post sample data and expected output/actions ?
0

LVL 10

Expert Comment

ID: 12102335
Here is what might be a barebones framework for what you want.   I'm sure the finished code would look quite different but at least it should give you a starting point...

declare @sectorPrice decimal (10,2), @price decimal (10,2), @diff decimal (10,2)
declare @steps int
select @sectorPrice  = 8.1, @price = 10.0
select @diff = abs(@sectorPrice - @price)
select @steps = @diff / 0.1

while (@steps > 0)
begin
if @price > @sectorPrice
select @price = @price - 0.1
if @price < @sectorPrice
select @price = @price + 0.1
print @price
select @steps = @steps - 1
end

Note that the above prints out the stesp BETWEEN price ans sector price.  You would need to amend the logic if you wanted to include price or sectorprice as part of the output.

AustinSeven
0

Author Comment

ID: 12102340
Hi Hilaire,

'price', taken from the table 'WBI' is right now at 100 and sector-price, saved as well in table 'WBI' is at 134,4. I now would like to count the 'price' up/(should work for down as well as the 'price' could be at 145) in steps of 0,1% (100,1, 100,2...) till it reaches 'sector-price' 134,4. Then I would like to save the last price before reaching 'sector-price', in this case ~134,25. I would like to have this done at once for the 12'000 securities.

I will be right now out of the office as I live in Europe. I'll check at home or at latest tommorrow. Thx.
ColumA
0

LVL 26

Expert Comment

ID: 12103467
Not sure I understood full requirement
Can you give it a try

create function getlastprice(@price decimal(15,5), @sectorprice decimal(15,5)) returns decimal(15,5) as
begin
declare @step decimal(15,5)
set @step = case when @price < @sectorprice then 1.001 else 0.999 end
while @price * @step < @sectorprice
set @price = @price * 1.001
return @price
end
go

select price, sectorprice, dbo.getlastprice(price, sectorprice) as computedprice from WBI
0

LVL 26

Expert Comment

ID: 12103569
Oops, don't use the function downwards for the moment (it will lopp forever), i'll fix it later if it's ok upwards
0

Author Comment

ID: 12109980
Do I understand right that the solution form AustinSeven goes for a stored procedure and the one of Hilaire for a public function? What would be the better way to go knowing that at the end, I have to implement one or two more tables in cross join? Public funtion would suit me better as I can use this in some other forms as well.
0

LVL 10

Expert Comment

ID: 12110083
Ultimately, either a stored proc or a function would provide a solution.   However, functions would generally be used where a value is returned and there isn't a 'side affect' as a result of calling the function.   So, on that basis, a function here is good.   Use of the function can also help to produce something close to a 'structured program' style and help to produce more understandable and maintainable code.   Ultimately, you might still end up writing a stored procedure to encapsulate the logic AND call something like Hilaire's function.

AustinSeven
0

Author Comment

ID: 12110181
If at the end, I have to go for a stored procedure again, then I rather start with a stored procedure. Am I right that this might be a performance-issue as well and will get a plus-point for the procedure with 12'000 rs to go? Anyway, I tried yours now but get an error saying wrong syntax near declare. Does there belong an AS and if yes, where, as I tried and still get the error?
ColumA
0

LVL 26

Accepted Solution

Hilaire earned 500 total points
ID: 12110266
>>If at the end, I have to go for a stored procedure again<<
You don't !
Using a stored procedure means you'll have to loop through all the records, using a cursor.

Putting the logic in a function means you can update all the rows with a single update

New version works upwards and downwards

create function getlastprice(@price decimal(15,5), @sectorprice decimal(15,5)) returns decimal(15,5) as
begin
declare @step decimal(15,5)
if @price < @sectorprice
begin
set @step = 1.001
while @price * @step < @sectorprice
set @price = @price * @step
end
else if @price > @sectorprice
begin
set @step = 0.999
while @price * @step > @sectorprice
set @price = @price * @step
end
return @price
end
go

-- To simulate all prices :
select price, sectorprice, dbo.getlastprice(price, sectorprice) as computedprice from WBI

--To update another field in same table for all 12000 rows
update WBI set simulatedprice = dbo.getlastprice(price, sectorprice)

Could you give it a try ?

0

Author Comment

ID: 12110358
ok Hilaire, makes sense. I get an error saying wrong syntax near 'go'

Is it correct that

-- To simulate all prices :
select price, sectorprice, dbo.getlastprice(price, sectorprice) as computedprice from WBI

--To update another field in same table for all 12000 rows
update WBI set simulatedprice = dbo.getlastprice(price, sectorprice)

has to be created in a separate procedure and if yes, where do I define that @price is dbo.WBI.price.... in this function?
Sorry for this basic question but I come from access and love all the program-helps and are new to SQL
0

LVL 26

Expert Comment

ID: 12110395
The "create function" statement (all the code between "create" ... and "go") should be executed separately in query analyser. Make sure the right DB is selected.
Select the code and press F5

You should get a message "commands completed successfully"

>>where do I define that @price is dbo.WBI.price.... in this function?<<
Once the function is created, when you call it you use positional parameters

so

select price, sectorprice, dbo.getlastprice(price, sectorprice) as computedprice from WBI
means that price (fully qualified name dbo.WBI.price is known as @price in the function

0

Author Comment

ID: 12111375
Have gone into query-analyzer and the right DB. Went to Function - NEW - scalar and pasted the code and F5. Got the confirmation, but can not see the entry under the Functions. When I create a new stored procedure with

ALTER PROCEDURE testsim
AS
update WBI

set simulatedprice = dbo.getlastprice(price, sectorprice)

it doesn't execute saying invalid object dbo.getlastprice

Any idea where the bug is?
0

LVL 26

Expert Comment

ID: 12111524
I never used a wizard to create a function in QA.
I just paste my code in QA, select/highlight the part I need to execute, and press F5 ...

Please make sure you didn't save my code as the template.

sp_helptext 'getlastprice'
in the currend db, then in the "master" db and let us know what you get

"invalid object" means either the function was not created or it was created in a wrong place/db

I  checked the code so it should compile.

Cheers

Hilaire
0

Author Comment

ID: 12111779
This works. Found out that I created the function in the master. Choosen my DB but haven't noticed that I have to choose it in the drop-down. So, my first step works. Let me work into this and I am sure that after, I have to come with further questions. I would be happy to have your support then again.
Thx
ColumA
0

LVL 26

Expert Comment

ID: 12111836
Feel free to post a follow-up if you need a little tweaking on the function above,
or a change on the calculation logic.

Regards

Hilaire
0

Author Comment

ID: 12112707
Changed a bit but already get a timeout executing the procedure. Is this because I have some NULLS or dividing by 0 or are 12'000 to much to go?
0

LVL 26

Expert Comment

ID: 12112810
>>Changed a bit<<
do you mean that you changed the function ?

>>Is this because I have some NULLS <<
If you kept my code "as is", null parameters should not make the function loop forever

>>or dividing by 0 <<
No division in my code above

>>are 12'000 to much to go? <<
To find out check completion time for smaller sets of data

-- limit any update to the first 50 records
set rowcount 50
-- issue the update statement and see how long it takes
update WBI
set simulatedprice = dbo.getlastprice(price, sectorprice)
-- swith to normal
set rowcount 0

HTH

Hilaire

0

Author Comment

ID: 12112960
I changed to code only to my original table. Have now set rowcount step by step till I got the timeout. So I got to the recordset which makes the timeout. I there have a price of 0 and a sectorprice of 4.5. I guess he can't count 0,1% from 0. Is it right and how to solve?
0

LVL 26

Expert Comment

ID: 12113050
Congrats for finding the "bad" records !

Increasing 0 by steps of 0.1 % remains 0 forever !!
I had forgotten to handle this case...

You could simply ignore these records
update WBI
set simulatedprice = dbo.getlastprice(price, sectorprice) where price <> 0

ANother option would be to set a rule to handle this case ?
- use a default value of 0.1  (that would cost several loops to reach sectorprice) when price = 0
- return sectorprice minus 0.1 % whithout looping when price = 0

What you fit your requirements best ?
0

Author Comment

ID: 12113105
As this is just the start and I feel sick by looking forward what the end should be :-) I go for the <>0 as at the end, there shouldn't be too much rst with 0
0

Author Comment

ID: 12140722
Hilaire, looked into my problem and saw that I have to forward more then 200 Parameters to the function. Do you still belive it will be the best solution with a function or do you think I should go for a procedure? As this looks very complex to me, would you like to help me to manage this step by step for 1000 points as it is important to me to have this run? If yes, I will post a question on monday. Thx for the Feedback.
ColumA
0

LVL 26

Expert Comment

ID: 12158610
ColumnA,
Sorry for not coming back sooner, I had a lot to do at my own site/job last days.
More than 200 parameters sounds way too much parameters to handle in a user-defined function.
Anyway will you design a form to pass more than 200 parameters to the function ??
I tend to think most of the parameters could be constants, or use pre-defined values depending on another param.
Most of the information could be stored in a dedicated table, and the procedure can then use these values.

I'm not sure I'll have enough time to give a much help today, but I might have a at night.

For the moment, I think you should post another question giving more details on the calculation algorithm and possible parameters.
You could also delete or rename then question "Hilaire - ...." as using my name in the question might might not help to draw attention from other experts.

I'll try to help as soon as you post more details, but other experts will sure be glad to give a helping hand.

Regards

Hilaire
0

Author Comment

ID: 12158696
Hi Hilaire, I don't need to have done this immediate. I also have the Quarter-End in front which I have to end first. We can handle this for weeks. I posted once the question with 500 points but got no feedback as it is hard to describe what the outcome should be. I think I post the question nxt monday as I have then one week off and have more experimental-time and give you a link here to my new posting.
Rgds
ColumA
0

LVL 26

Expert Comment

ID: 12159283
Sounds good,
I should have more time next week.

>>it is hard to describe what the outcome should be<<
If you have a big document that explains the full business logic and could be turned into a decent algorithm, I'm sure this would greatly help.

You could upload it (or a 'light' version to remove confidential information if need be) in a yahoo briefcase and make it available for EE experts.

Feel free to notify me in this thread when you post the new thread.

Regards

Hilaire
0

Author Comment

ID: 12219593
Hi Hilaire

I have my week holiday and try to get my problem fixed. Here's my posting

http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21155272.html

I know it sound difficult but ask me anything you need to know. I try to log in every day at night Europe time, as I have to take care my two kids at daytime ;-). Thx for your support. For your better understanding of my background, I am self-employed in the financial-sector and Computer is my hobby, so I try to get a programme that might help me at work. This is why I am not Pro in this area. Thx. ColumA
0

Author Comment

ID: 12235482
Hi Hilaire

Just noticed in your CV that you live in France. Bonjour. I live in the Switzerland. Not that far away.

As I expected on my http://www.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_21155272.html question, nobody responds. Seems that I don't explain it the right way. I would be happy if we can go through it step by step. Thx for have a look.
Rgds
ColumA
0

LVL 26

Expert Comment

ID: 12235546
Hi ColumA,

I'll have a look today.

Hilaire

0

Author Comment

ID: 12257562
Thx. Just started with the variables at the beginning. Posted my first problem in the Q21155272. Merci.
ColumA
0

Author Comment

ID: 12279661
Hilaire, can you pls help me? How can I use the data from 'F1Provisorium' in further procedures or use them for an update in a table like GFHilfstabelle1? My code looks like:

ALTER PROCEDURE Kurszielsimulation
AS
--- VARIABLEN
DECLARE @Provisorium decimal (15,5), @F1Provisorium real, @F1_B_Provisorium real
DECLARE @steps decimal (15,5)
--- WERTZUTEILUNG
SELECT              @Provisorium = Kurs , @F1Provisorium = F1, @F1_B_Provisorium = F1_B
FROM dbo.GFHilfstabelleV1

--- BASISINFO
SELECT     dbo.GFBerechnungsbasis.*, dbo.GFHilfstabelleV1.Symbol
FROM         dbo.GFHilfstabelleV1 CROSS JOIN
dbo.GFBerechnungsbasis
WHERE     (dbo.GFBerechnungsbasis.Berechnungsvariante = '1')

begin
if @F1Provisorium < @F1_B_Provisorium
begin
set @steps = 1.001
while @F1Provisorium * @steps < @F1_B_Provisorium
set @F1Provisorium = @F1Provisorium * @steps
end
else if @F1Provisorium > @F1_B_Provisorium
begin
set @steps = 0.999
while @F1Provisorium * @steps > @F1_B_Provisorium
set @F1Provisorium = @F1Provisorium * @steps
end
end
return @F1Provisorium

Thx.
ColumA
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question