Calculate new observations with existing

[Tobias Buchfelner]

Dear members,


I Need your help with a Question regarding generating new observations.

I have several Investment funds with several Holdings in it. Some of the funds report each month, some of them each quarter, and some of them usually each quarter but also sometimes in between. What I Need is an Observation for each Holding in the fund at the end of the month with the corresponding variables.

What I have at the moment Looks like this:

Fund	Holding	Date	Mcap
1	abc	31.12.2010	$10.000.000,00
1	abc	31.03.2011	$11.000.000,00
1	abc	30.04.2011	$11.200.000,00
1	abc	30.06.2011	$13.000.000,00
1	abc	30.09.2011	$12.500.000,00
1	abc	31.12.2011	$13.500.000,00
1	zyx	31.12.2010	$2.000.000,00
1	zxy	31.03.2011	$2.100.000,00
1	zyx	30.06.2011	$1.950.000,00
1	zxy	30.09.2011	$1.500.000,00
1	zyx	31.12.2011	$1.600.000,00
1	zxy	31.03.2012	$1.610.000,00
1	zyx	30.06.2012	$1.800.000,00
2	def	31.12.2010	…
2	def	31.03.2011
2	def	30.06.2011
2	ghi	31.12.2010
2	ghi	31.03.2011
…	…	…

After the calculations it should look like this:

Fund	Holding	Date	Mcap
1	abc	31.12.2010	$10.000.000,00
1	abc	31.01.2011	$10.333.333,00
1	abc	28.02.2011	$10.666.666,00
1	abc	31.03.2011	$11.000.000,00
1	abc	30.04.2011	$11.200.000,00
1	abc	31.05.2011	$12.100.000,00
1	abc	30.06.2011	$13.000.000,00
1	abc	31.07.2011	…
1	abc	31.08.2011	…
1	abc	30.09.2011	…
1	abc	31.10.2011	…
1	abc	30.11.2011	…
…	…	…	…
1	zyx	31.12.2010	$2.000.000,00
2	zyx	31.01.2011	$2.333.333,00
2	zyx	28.02.2011	$2.666.666,00
2	zyx	31.03.2011	$2.100.000,00

The calucations for the second row would look like 10.000.000+1/3*(11.000.000-10.000.000) and in the sixth row like this 11.200.000+1/2*(13.000.000-11.200.000).

My Data has a few Million observations with something around 50 variables.

I would appreciate it a lot if someone could help me with my Problem.

[Dave Airey]

I think you need to start by filling in all the dates for each fund/holding level. I think you can do this using cross and merge. Below I was playing with each command to show this:

clear
input group
1
2
end
save group.dta, replace
clear
input level
1
2
3
4
5
end
save level.dta, replace

use group.dta, clear
cross using level.dta
generate measure = .
save level.dta, replace

clear
input group level measure
1 1 100
1 3 130
1 5 150
2 1 100
2 5 150
end
save main.dta, replace

use main, clear
merge 1:1 group level using level.dta
sort group level
list

[Dave Airey]

Also see this recent post as a way of filling in missing dates: https://www.statalist.org/forums/for...values-by-date.

[Romalpa Akzo]

A good combination of fillin and ipolate will solve the issue.

Then Tobias, if you are still seeking for a specific code, you should read FAQ and use dataex to give out here a small example of your data.

[Tobias Buchfelner]

Also see this recent post as a way of filling in missing dates: https://www.statalist.org/forums/for...values-by-date.

Thank you so far, I think tsfill could be the right approach for the start.

The problem is, that I get the error "repeated time values within panel", probably because the same date arises over and over again.

Here a sample of my data: