Contact US

Question

How to calculate price development

german12 (Programmer)

(OP)

19 Jun 22 11:44

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 (Programmer) · Answer 1 · 2022-06-19 12:22:50

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

GriffMG (Programmer) · Answer 2 · 2022-06-19 12:23:27

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 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 (Programmer) · Answer 3 · 2022-06-19 12:40:16

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 (Programmer) · Answer 4 · 2022-06-19 12:53:47

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 (Programmer) · Answer 5 · 2022-06-19 13:10:43

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

Chris Miller (Programmer) · Answer 6 · 2022-06-19 15:55:24

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 (Programmer) · Answer 7 · 2022-06-19 16:00:43

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

Mike Lewis (Programmer) · Answer 8 · 2022-06-19 16:27:36

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 (Programmer) · Answer 9 · 2022-06-19 16:39:26

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 (Programmer) · Answer 10 · 2022-06-19 16:57:01

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

Mike Lewis (Programmer) · Answer 11 · 2022-06-19 17:03:50

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

german12 (Programmer) · Answer 12 · 2022-06-19 17:07:06

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 (Programmer) · Answer 13 · 2022-06-19 17:20:02

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 (Programmer) · Answer 14 · 2022-06-19 17:51:22

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

german12 (Programmer) · Answer 15 · 2022-06-19 18:30:14

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 (Programmer) · Answer 16 · 2022-06-19 19:42:26

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 (Programmer) · Answer 17 · 2022-06-19 20:41:08

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

Chris Miller (Programmer) · Answer 18 · 2022-06-19 20:58:13

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 (Programmer) · Answer 19 · 2022-06-20 11:03:57

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

Chris Miller (Programmer) · Answer 20 · 2022-06-20 12:26:27

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

Mike Lewis (Programmer) · Answer 21 · 2022-06-20 14:56:13

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

GriffMG (Programmer) · Answer 22 · 2022-06-20 15:17:21

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

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

german12 (Programmer) · Answer 23 · 2022-06-20 15:53:57

Mike Lewis:

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

Thanks
Klaus

Peace worldwide - it starts here...

Mike Lewis (Programmer) · Answer 24 · 2022-06-20 15:55:55

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

german12 (Programmer) · Answer 25 · 2022-06-20 16:33:53

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

Contact US

Come Join Us!

Posting Guidelines

How to calculate price development 4

How to calculate price development

How to calculate price development

RE: How to calculate price development

CODE

RE: How to calculate price development

CODE

RE: How to calculate price development

RE: How to calculate price development

RE: How to calculate price development

CODE -->

RE: How to calculate price development

RE: How to calculate price development

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

RE: How to calculate price development

RE: How to calculate price development

RE: How to calculate price development

CODE -->

RE: How to calculate price development

Quote:

RE: How to calculate price development

RE: How to calculate price development

RE: How to calculate price development

CODE

RE: How to calculate price development

RE: How to calculate price development

RE: How to calculate price development

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

RE: How to calculate price development

RE: How to calculate price development

CODE -->

RE: How to calculate price development

CODE

CODE

RE: How to calculate price development

Quote:

RE: How to calculate price development

CODE

RE: How to calculate price development

RE: How to calculate price development

RE: How to calculate price development

Red Flag This Post

Red Flag Submitted

Reply To This Thread

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

Join Tek-Tips® Today!

How to calculate price development
4

Join Tek-Tips^® Today!