Computation of Market Minimum Price using Price Change Data

[Christoph Winter]

Hi all,

I am using Stata/SE 12.1.

I have data that looks like the example below. Suppose there are three firms in a market who change their prices for a certain product frequently.

For each observation (firm-date combination), I want to compute the minimum price in the market and save this price in a variable (called min_price).

The entries should look like min_price_shouldbe.

Unfortunately, I don't know how to get there. More precisely, I don't know how to translate the following conditions into Stata code:
1) Only look at price changes with date < date[_n]
2) Only consider the last price change (with date < date_[n]) of each competitor

Any ideas?

Best,
Christoph

clear
input id  price_change  str16 date_help min_price min_price_shouldbe
1 109 "21dec2014" . 108
1 105 "25jan2015" .  100
1 113 "26feb2015" . 58
2 108 "15dec2014" . 108
2 156 "01feb2015" . 100
2 58 "20feb2015" . 58
3 455 "01jan2015" . 108
3 100 "15jan2015" . 100
3 390 "16feb2015"  . 105
end

gen date = date(date_help,"DMY")
format date %td

sort date

[Robert Picard]

I think the following does what you want. I assume that there's only one observation per id and date.

clear
input id  price_change  str16 date_help min_price min_price_shouldbe
1 109 "21dec2014" . 108
1 105 "25jan2015" .  100
1 113 "26feb2015" . 58
2 108 "15dec2014" . 108
2 156 "01feb2015" . 100
2 58 "20feb2015" . 58
3 455 "01jan2015" . 108
3 100 "15jan2015" . 100
3 390 "16feb2015"  . 105
end

gen date = date(date_help,"DMY")
format date %td

* the following should be true
isid date id, sort

* track, for each id, the most recent price
levelsof id, local(ids)
foreach i of local ids {
    gen lastp`i' = price_change if id == `i'
    replace lastp`i' = lastp`i'[_n-1] if mi(lastp`i')
}

* on any given date, the minimum price
egen minp = rowmin(lastp*)

list

[Christoph Winter]

Thank you very much for the quick response.

So basically the idea is that you generate a new variable with the last price of each competitor and then you generate the rowmin.
I think that works fine for this task.

However, in the next step, I want to compute a measure of price dispersion.
So its perfect to know the price on that date, but now I also need to include the price changes of the competitors in the future.
Do you think adding a new variable with the time of change for each lastp* will help? I thought about weighted price differences given the duration of the price in place.
However, do you have an idea how to handle situations where firm1 doesn't change its price but a competitor changes its price twice?

I think it is even difficult to handle a simple example like this:

Suppose, on 1jan2015 all firms price at 100$.
Firm1 doesn't change the price.
Firm2 changes the price to 50$ on 1feb2015.
Firm3 changes the price to 0$ on 1march2015.

Let's take price - minprice as price dispersion measure.

So in the example price dispersion would be:
firm 1: for jan 0$, for feb 50$ and for march 100$
firm 2: for jan 0$, for feb 0$ and for march 50$
firm 3: for jan 0$, for feb 50$ and for march 0$

Any ideas?

[Robert Picard]

I'm sorry but I don't quite get what you are trying to do. I don't know what a measure of price dispersion should look like. You sometimes talk about price change but in some other place you refer to actual price. It would help if you set up another data example that illustrates the problem and that includes what you think the correct calculation should be.

[Christoph Winter]

Sorry for the confusion. In the data I have price changes.
However, actually I am interested in prices at a given date, respectively price dispersion at a given date.
A daily measure for price dispersion in a market with three firms (i= {1,2,3}), for example, could be mean(price_i - min_price) for that date.

That is a rather easy task, when setting up a daily panel and translating price changes in actual prices. Rule: Take last price change and carryforward until next price change.

However, because of memory restrictions I am not able to construct this panel with all dates for all ids.
So I have to work with the price_change structure, or I have to develope a method that aggregates the data, say on a weekly level, without loosing information.

The idea was the following. I know when a firm changes its price. Therefore, I know how long this price is active and I can create a duration variable.
The task is to find the average price dispersion for this duration. Obviously, the prices of the competitors may change and so may the min_price in the market.

So, I need some method to calculate the average min_price in the market for the given duration.

The idea is the following:

Lets look at the cheapest firms in the market
21dec2014 to 14jan2015: firm2; price 108
15jan2015 to 15feb2015: firm3; price 100
16feb2015 to 25feb2015: firm1; price 105
26feb2015 to 28feb2015: firm1; price 113

-> price - min price for firm 1:

21dec2014 to 14jan2015: 109-108; duration: 24 days
15jan2015 to 24jan2015: 109-100; duration: 9 days
25jan2015 to 15feb2015: 105-100; duration: 22 days
16feb2015 to 25feb2015: 105-105; duration: 10 days
26feb2015 to 28feb2015: 108-108; duration: 3 days
---
days: 68

-> mean price dispersion for firm1 = ((109-108)*24+(109-100)*9+(105-100)*22+0+0)/68

clear
input id  price_change str16 date_help duration min_price min_price_shouldbe
1 109 "21dec2014" 35 . 108
1 105 "25jan2015" 32 .  100
1 113 "26feb2015" . . 58
2 108 "21dec2014" 42 . 108
2 156 "01feb2015" 19 . 100
2 58 "20feb2015" . . 58
3 455 "21dec2014" 25 . 108
3 100 "15jan2015" 32 . 100
3 390 "16feb2015"  . . 105
end

gen date = date(date_help,"DMY")
format date %td

bysort id (date): gen duration2 = date[_n+1]-date[_n]

gen mean_pricedispersion = ((109-108)*24+(109-100)*9+(105-100)*22+0+0)/68 if id == 1

[Robert Picard]

I'm still very fuzzy on what you are trying to do exactly. It doesn't help that your dates do not match the ones in the data example. Here's a different approach that follows your description of how to get to the dispersion measure:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id int(price date)
1 109 20078
1 105 20113
1 113 20145
2 108 20078
2 156 20120
2  58 20139
3 455 20078
3 100 20103
3 390 20135
end
format %td date

* the following should be true
isid date id, sort
list, sepby(date)

* rectangularize to have an obs for each id on each date
fillin date id
list, sepby(date)

* carry over the price
bysort id (date): replace price = price[_n-1] if mi(price)
list, sepby(id)

* note the number of days until the next date; last date counts for 1 day
by id: gen days = date[_n+1] - date - 1
replace days = 1 if mi(days)
list, sepby(id)

* reorder by date and calculate min price on that date
bysort date (id): egen mprice = min(price)
list, sepby(date)

* the difference in price over days
gen dpd = (price - mprice) * days
list, sepby(date)

* calculate dispersion measure
sort id date
by id: egen daystot = total(days)
by id: egen dpdtot = total(dpd)
gen dispersion = dpdtot / daystot
list, sepby(id)

[Christoph Winter]

Thanks again! Well, maybe that is the only way how to handle it and I need to split the data into parts in order to stay within the RAM limits.