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

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
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
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
CODE
Regards
Griff
Keep
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
Bananen 1,49 - 1,19 = -0,30 = - 44,7 %
Regards
ing
Griff
Keep
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
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
Leaving percentages to one side for the moment, you might try something like this:
CODE -->
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
Chriss
RE: How to calculate price development
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.
Klaus
Peace worldwide - it starts here...
RE: How to calculate price development
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
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
CODE -->
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
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
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
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
CODE
Chriss
RE: How to calculate price development
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
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
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...
RE: How to calculate price development
Or more.
Chriss
RE: How to calculate price development
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 -->
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
I don't know why you do this:
CODE
For the late prices you actually need all prices with Datum later than the minimum date.
So the second query should be:
CODE
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
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
CODE
Regards
ing
Griff
Keep
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
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
Mike
__________________________________
Mike Lewis (Edinburgh, Scotland)
Visual FoxPro articles, tips and downloads
RE: How to calculate price development
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...