×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to calculate price development
4

How to calculate price development

How to calculate price development

(OP)
I have a table like this:




As you can see some prices had increased or decreased (more than one line with the same product), and some had not so far (only one line)
In the example these are Bananen and Butter.

I only want to have a new table for things which have changed.

like this
Bananen 1,49 - 1,19 = -0,30 = - 44,7 %
Butter 1,65 - 2,29 = +0,64 = + 38.8 %


I only want to see, where prices got up or down.
Not those with one line - they are unchanged.
This means that some products can have more than one line - that depends how often prices where changed.
(a min/max calculation within a product).

How would you code that?
As I see min/max prices is only possible for the whole table, but not by product.

I can imagine to select a distinct(product) into a 2nd table to have each product only once.
From there perhaps scan that table and compare it with the 1st. table and let a function write into an array per product.

But maybe it is easier perhaps.

Thanks for help
Klaus

Btw - Butter has become very expensive in Germany - the price is for 250 g +39 % within 2 months!
How is that in your country?









Peace worldwide - it starts here...

RE: How to calculate price development

You should really normalize your data.

Then you'd have products like "BANANA" standing for themselves, products of no vendor and at no qty/amunt/weight adn with no price and date.
You'd have product offers with amount/qty and price by vendors at dates, which makes offers comparable.

Now only judging by same name, sort by name with first prio, date with second prio and then compute relative differences.

Something like that:

CODE

Select product, price, datum, vendor, cast(0 as b null) as pricechangepercentage from prices order by product, datum into cursor pricedevelopments READWRITE
currentproduct = ""
currentprice = 0.01
Scan
   If currentproduct == alltrim(product)
      replace pricechangepercentage With 100*(price/currentprice-1)
      currentprice = price
   Else
      currentproduct =  alltrim(product)
      replace pricechangepercentage With .null.
      currentprice = price
   Endif
Endscan 

Untested.

If you mainly want to compare the price development for same vendor then just change the order by to reflect the ordering priorities, i.e. product, vendor, datum instead.


The major idea is to get the history of what to compare in record order and then process record by record, using previous record data in variables. There's nothing like LAG and LEAD of MSSQL in VFP so that's what you mainly have. SQL will be no help here, there might be convoluted ways to get there, but VFP doesn't have PARTITION BY, for example, which you would use within other databases. If you want to go for better options in SQL then actually change to SQL Server, there are express or developer edition. But then also first learn a lot more about database design and data normalization.

Chriss

RE: How to calculate price development

Being old school I would do it like this:

CODE

CLOSE ALL

SELECT 0
USE D:\$INCOMING\MYTABLE EXCLUSIVE
INDEX ON MYTABLE.PRODUCT+DTOS(MYTABLE.DATUM) TAG MYINDEX
GO TOP
DO WHILE !EOF()
	m.PRODKEY = MYTABLE.PRODUCT
	m.PRICE = MYTABLE.PRICE
	SKIP
	DO WHILE .NOT. EOF() .AND. MYTABLE.PRODUCT= m.PRODKEY
		IF MYTABLE.PRICE <> m.PRICE
			? MYTABLE.PRODUCT, MYTABLE.PRICE, "-", m.PRICE, "=" ,m.PRICE-MYTABLE.PRICE, "=", ((m.PRICE-MYTABLE.PRICE)/m.PRICE)*100,"%"
			m.PRICE = MYTABLE.PRICE
		ENDIF
		SKIP
	ENDDO
ENDDO 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0

RE: How to calculate price development

Also I don't quite follow your percentage calc, isn't that -25% ish

Bananen 1,49 - 1,19 = -0,30 = - 44,7 %

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0

RE: How to calculate price development

Well, I first had this totally wrong, too.

But then it's simple if you think of some extreme cases. A new price of 0 means -100%, a new price double as before means +100%. Prices just shouldn't ever drop to 0 or that causes division by zero errors.

And for the sample case that's 100*(1.19/1.49-1), about -20%. But since the price of 1.49 was after 1.19 that's not a price drop. I mean, you can order any way you want and compute changes in that order, but the usual order of history is when date differences are rising.

Chriss

RE: How to calculate price development

I'm also not sure about the percentages. I can't see what you are expecting the percentages to show.

Leaving percentages to one side for the moment, you might try something like this:

CODE -->

SELECT Product, SUM(Price) FROM Prices ;
  GROUP BY Product HAVNG Count(*) > 1 

That should give you the absolute increase or decrease per product where the price has changed. Is that what you are looking for? If so, it could be a good starting point.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to calculate price development

I don't know, Mike, this simply gives the sum. Something useful with GROUP BY would be the average price, AVG(price) as averageprice.

Chriss

RE: How to calculate price development

(OP)
Thanks for your answers to all of you.

Sorry, of course you all are right.
Bananas - I did not consider the date - they increased by 25,2 %

Mike L:
As your code is very short (the shortest so far) - I tried it - however the result is this as it summarizes the prices - so I can't see their developement.


Quote (Mike - SELECT Product, SUM(Price) FROM Prices ; GROUP BY Product HAVNG Count(*) > 1 )



leads to:


Klaus






Peace worldwide - it starts here...

RE: How to calculate price development

Klaus and Chris,

Yes, I realise that my solution only calculates the sum, not the average or the percentage. My problem is that, when calculating the percentage, I don't know what you want to use as the base price. Is it the total increase over the lowest price, or the earliest price, or the average price, or what?

Actually, my code doesn't even calculate the absolute net increase or decrease, as I originally stated. It clearly needs a bit more work.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to calculate price development

(OP)
Mike - the base price is always the price with the oldest date.
In the example - bananas = 31.03.22 (german dates = dd.mm.yy)
- Butter = 07.03.22

I want to be calculated the difference between youngest versus oldest price via date column.
For Butter the youngest price = 2,29 (31.05.22)

so for Butter there is price-increase from 1,65 to 2,29 to be calculated.


Greetings
Klaus

Btw: Sunflower Oil we paid 4 month ago 1,99 - now the price = 4,50 (horrible)

Peace worldwide - it starts here...

RE: How to calculate price development

Would something like this be a bit nearer:

CODE -->

SELECT Product, AVG(Price) AS AvPrice, MIN(Datum) AS MinDatum ;
  FROM Prices ;
  GROUP BY Product HAVNG Count(*) > 1 '
  INTO CURSOR tempPrices

SELECT p1.Product, ((p2.avPrice - p1.Price / p1.Price) * 100) AS pcnt_change ;
  FROM Prices p1 ;
  JOIN tempPrices p2 ON p1.Product = p2.Product AND p1.Datum = p2.MinDatum ;
  INTRO CURSOR Results 

I think this will calculate the percentage increase of the average price over the earliest price. But I did it in a hurryadn didn't try to test it, so don't be surprised if it is rubbish.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to calculate price development

Quote:

I want to be calculated the difference between youngest versus oldest price via date column

Klaus, that's completely clear now. But I posted my latest code before I saw that, so clearly mine is not what you want.

It's getting late now and I will have to close down, but I might come back to it another time if you have not already got a solution.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to calculate price development

(OP)
Griff - your code leads to



As I clarified with my response to Mike (see above)- I only want one difference to be calculated which is always the difference between youngest price versus oldest price.
This can be defined by the column "Datum" (= date())
It is not necessary to show all stages between.
For Butter e.g. there is only one difference to be calculated, although there are 3 lines in the example.

Regards
Klaus

Peace worldwide - it starts here...

RE: How to calculate price development

(OP)
Mike - this is the result now:



I think, the direction in your code is nice, but unfortunately there is still not the correct result.

Butter from 1,65 to 2,29 is not 101 % increase.



Klaus


Peace worldwide - it starts here...

RE: How to calculate price development

If you want the price change in comparison to the first known price, then just change this to:

CODE

Select product, price, datum, vendor, cast(0 as b null) as pricechangepercentage from prices order by product, datum into cursor pricedevelopments READWRITE
currentproduct = ""
currentprice = 0.01
Scan
   If currentproduct == alltrim(product)
      replace pricechangepercentage With 100*(price/currentprice-1)
      * currentprice = price
   Else
      currentproduct =  alltrim(product)
      replace pricechangepercentage With .null.
      currentprice = price
   Endif
Endscan 

Chriss

RE: How to calculate price development

(OP)
Chriss, thank you again.
The result of your code is this



Well done!
That is what I wanted.

Of course many thanks to all other contributions here.
I never would have found out the right way so fast....

Regards
Klaus




Peace worldwide - it starts here...

RE: How to calculate price development

SET NULLDISPLAY TO '' and it even looks nicer, especially on a form, to see .NULL. in a Browse as developer is okay, of course.

Now you could of course only take products with at least 2 prices, but I guess with all the data you'll collect this will become the norm and there'll be only few products to remove.

Chriss

RE: How to calculate price development

(OP)
Chriss - yes that looks better



and with

Quote (SELECT * FROM pricedevelopments WHERE pricechangepercentage <> 0 INTO CURSOR changedprize)


I have a table which shows prize-changes only



but that causes a disadvantage, because the base-prize is lost.....as that base-prize also has the value 0

Klaus




Peace worldwide - it starts here...

RE: How to calculate price development

Yes, so that's not how you filter it. You remove products having only COUNT(*)=1. Again, you should not need to do that at all, since sooner or later you have 2 prices of everything.
Or more.

Chriss

RE: How to calculate price development

Klaus (and others),

First, apologies for my lousy code from yesterday. It was the result of trying to do too many things at the same time.

I've had another bash at it in the fresh light of a new day. Here is my latest attempt:

CODE -->

* Create a cursor containing the price corresponding to the earliest date per product
SELECT p2.PRODUCT, p2.Price, p2.Datum ;
  FROM Prices p2 JOIN ;
  (SELECT p1.PRODUCT, MIN(Datum) AS MinDatum FROM Prices p1 GROUP BY p1.PRODUCT HAVING COUNT(*) > 1) p1 ;
  ON p2.PRODUCT = p1.PRODUCT AND p2.Datum = p1.MinDatum ;
  INTO CURSOR EarlyPrices ;

* Create a cursor containing the price corresponding to the lates date per product
SELECT p2.PRODUCT, p2.Price, p2.Datum ;
  FROM Prices p2 JOIN ;
  (SELECT p1.PRODUCT, MAX(Datum) AS MaxDatum FROM Prices p1 GROUP BY p1.PRODUCT HAVING COUNT(*) > 1) p1 ;
  ON p2.PRODUCT = p1.PRODUCT AND p2.Datum = p1.MaxDatum ;
  INTO CURSOR LatePrices

* Using data from the two cursor, calculate the percentage price change
SELECT ep.PRODUCT, ((lp.Price - ep.Price) * 100 / ep.Price) AS Pcnt_Change ;
  FROM EarlyPrices ep FULL OUTER JOIN LatePrices lp ON ep.PRODUCT = lp.PRODUCT 

If I've got this right, the above code will first determine the prices corresponding to the earliest and latest dates, and then calculate the percentage increase or decrease between those prices compared to the earliest price.

A cleverer SQL programmer than me would combine the three statements into one. I'll leave than as an exercise for others to do.

NOTE: Above code now amended as per Chris's comments - see below.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to calculate price development

Mike,

I don't know why you do this:

CODE

MAX(Datum) AS MinDatum 

For the late prices you actually need all prices with Datum later than the minimum date.

So the second query should be:

CODE

SELECT p2.PRODUCT, p2.Price, p2.Datum ;
  FROM Prices p2 JOIN ;
  (SELECT p1.PRODUCT, MIN(Datum) AS MinDatum FROM Prices p1 GROUP BY p1.PRODUCT HAVING COUNT(*) > 1) p1 ;
  ON p2.PRODUCT = p1.PRODUCT AND p2.Datum > p1.MinDatum ;
  INTO CURSOR FurtherPrices 

Unless you mean to only pick out the latest records, too. But then do MAX(Datum) AS MaxDatum and compare p2.Datum = p1.MaxDatum

Chriss

RE: How to calculate price development

Quote:

I don't know why you do this:
MAX(Datum) AS MinDatum

I can explain that. It's called a typing error. Thanks for pointing it out. I've now fixed it.

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to calculate price development

Klaus, I think you may have missed something out from my code, that's not the result I get, there seem to be extra products in your example happy shades

CODE

BANANEN 1KG                     1.49 -          1.19 =          -0.30 =                       -25.2101 % 
BUTTER                          2.09 -          1.65 =          -0.44 =                       -26.6667 % 
BUTTER                          2.29 -          1.65 =          -0.64 =                       -38.7879 % 

Regards

Griff
Keep Smileing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you.

There is no place like G28 X0 Y0 Z0

RE: How to calculate price development

(OP)
Mike Lewis:



Mike - I have tested your amended code - and it works as to be seen above.

Thanks
Klaus

Peace worldwide - it starts here...

RE: How to calculate price development

Thanks for letting me know, Klaus. Glad it actually worked (finally).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

RE: How to calculate price development

(OP)
Griff -

I used your code as follows:

CLOSE data
USE prices
*That is the table I mentioned at the beginning
*as you call it mytable I just copied it to mytable


COPY TO mytable
*This is the file I use in your code
*************************************************************

*************************************************************
CLOSE DATABASES
CLEAR



SET DATE german

*Your code starts here:

SELECT 0
USE MYTABLE EXCLUSIVE
INDEX ON MYTABLE.PRODUCT+DTOS(MYTABLE.DATUM) TAG MYINDEX
GO TOP
DO WHILE !EOF()
m.PRODKEY = MYTABLE.PRODUCT
m.PRICE = MYTABLE.PRICE
SKIP
DO WHILE .NOT. EOF() .AND. MYTABLE.PRODUCT= m.PRODKEY
IF MYTABLE.PRICE <> m.PRICE
? MYTABLE.PRODUCT, MYTABLE.PRICE, "-", m.PRICE, "=" ,m.PRICE-MYTABLE.PRICE, "=", ((m.PRICE-MYTABLE.PRICE)/m.PRICE)*100,"%"
m.PRICE = MYTABLE.PRICE
ENDIF
SKIP
ENDDO
ENDDO

with that result now




It is the right calculation, however I can not follow that the result for BUTTER is still different against yours....


I think that has perhaps something to do, that you used DTOS(Datum), which leads to your format for a date (YYMMDD)
However I noted in original our format (german = dd.mm.yy)


Please compare...

Anyway thanks for your comment.
I have to admit, that within my response before there must have been another error caused by myself.

Regards
Klaus


Peace worldwide - it starts here...

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close