How to calculate price development 4

[german12]

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

 

[Chris Miller]

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:

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

 

[GriffMG]

Being old school I would do it like this:

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 [Smile]ing

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
​

 

[GriffMG]

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 [Smile]ing

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
​

 

[Chris Miller]

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

 

[Mike Lewis]

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:

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

 

[Chris Miller]

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

 

[german12]

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.

leads to:

Klaus






Peace worldwide - it starts here...

 

[Mike Lewis]

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

 

[german12]

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

 

[Mike Lewis]

Would something like this be a bit nearer:

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

 

[Mike Lewis]

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

 

[german12]

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

 

[german12]

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

 

[Chris Miller]

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

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

 

[german12]

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

 

[Chris Miller]

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

 

[german12]

Chriss - yes that looks better

and with

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

 

[Chris Miller]

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

 

[Mike Lewis]

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:

* 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