Price Simulation in stored procedure

Hi columA,

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

Could you post sample data and expected output/actions ?

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

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

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

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

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.

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

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

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

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

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?

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.

please issue a
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

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

Glad I could help.
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

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?

>>Changed a bit<<
do you mean that you changed the function ?
If so, please post your amended code.

>>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

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?

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 ?

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

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

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

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

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

Hi Hilaire

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

https://www.experts-exchange.com/questions/21155272/Complicated-PriceSimulation.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

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 https://www.experts-exchange.com/questions/21155272/Complicated-PriceSimulation.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

Hi ColumA,

I'll have a look today.

Hilaire

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

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