Calculating average available funds between two dates.

[Frans Vinken]

Hi,

I am currently working with a dataset on SPAC (special purpose acquisition company) IPOs. For every SPAC, I have the IPO date, and the date an announcement is made about liquidation/merger of the SPAC.
In the SPAC market, at time t there is an amount of funds available that is used to take over another company. I have another dataset that states the amount available at time t. Now I want to compute what the average amount
of funds that were available between the IPO date and the announcement date, but I have difficulties merging these data.

The calculation should look something like this: sum(cum_funds_in_market(t))/sum(day(_n)), with the sum being all the days between IPO date and announcement date. I have attached the funds_in_spac_market dataset as attachment.

My main question is how to link these two datasets, so ultimately I have some kind of time-series for every company that starts at IPO date and ends at announcement date, with in between the corresponding funds available in SPAC market.

Is there anyone who have an idea how I could do this? Please let me know if it is not clear. Thanks in advance.

Kind regards,
Frans

[Carlo Lazzaro]

Frans:
as you may already know, nobody on this forum would ever download spreadsheets, due to the risk of active contents.
Please read (and act on) the FAQ on how to share what you typed and what Stata gave you back and on -dataex-. Thanks.

[Frans Vinken]

The dataset on IPO & announcement date look like this:

clear
input long company_id int(ipo_date announcement_date)
3612770 23051 23237
3572827 22932 23307
3564151 22897 23075
3555068 22862 23042
3540034 22812 23310
3538689 22806 23426
3531156 22776 23013
3515292 22713 22897
3513543 22706 23163
3512118 22701 22882
3512224 22701 23016
3511972 22700 22930
3506555 22685 23068
3505728 22683 23068
3504993 22680 23139
3504402 22679 22957
3503270 22677 22900
3503214 22676 23232
3499733 22666 23230
3499102 22665 23184
end
format %tdnn/dd/CCYY ipo_date
format %tdnn/dd/CCYY announcement_date

I want to calculate the average funds_in_spac_market between ipo_date and announcement_date. The dataset on funds_in_spac_market looks like this:

clear
input int date double cum_funds_available
20820      0
20827      0
20834    400
20841    575
20848    575
20855    575
20862    575
20869    575
20876    575
20883    575
20890    575
20897    875
20904    875
20911    910
20918 1061.5
20925 1109.5
20932 1109.5
20939 1109.5
20946 1109.5
20953 2176.5
end
format %tdnn/dd/CCYY date

For example, if ipo_date is 01/01/2017 and announcement date is 01/01/2018, I want to know what the average funds_in_spac_market were in 2017. How can I merge these datasets so I can calculate this average?

Thanks.

[Carlo Lazzaro]

Frans:
I do not think this is feasible if the company_id is not available for the second dataset too.

[Clyde Schechter]

I think O.P. means that he has data on the total funds in the SPAC market, not company-level data, and wants to use the mean value of total funds in his data. If I have that right, he can do this:

use spac_ipos, clear
rangejoin date ipo_date announcement_date using spac_market_funds
collapse (mean) cum_funds_available, by(company_id ipo_date announcement_date)

Replace the italicized material by the actual names of the corresponding data sets.
-rangejoin- is written by Robert Picard and is available from SSC. To use it, you must also install -rangestat-, by Robert Picard, Nick Cox, and Roberto Ferrer, also from SSC.

Note: In the example data provided, none of the dates in the spac market funds data set falls into the interval between ipo and announcement dates for any of the observations in the spac IPOs data set, so the code is not really tested. But I am highly confident it is correct. In the future, it would be helpful to choose data examples more carefully so that they can actually illustrate the problem at hand.

[Frans Vinken]

Thank you for your reaction Clyde. The code you provide is still not exactly what I am looking for. In order to clarify myself I have made an example dataset with the desired result. I want to combine these datasets in order to create panel-data in order to calculate the mean funds_available between IPO_date and announcement_date.

The cross-sectional dataset with IPO_date and announcement_date per id:

clear
input byte id int(IPO_date announcement_date)
1 21915 21921
2 21921 21933
3 21927 21934
end
format %tdnn/dd/CCYY IPO_date
format %tdnn/dd/CCYY announcement_date

And the time-series dataset for the funds_available at time date_funds.

clear
input int(date_funds funds_available)
21915 100
21916 100
21917 200
21918 200
21919 250
21920 270
21921 270
21922 320
21923 380
21924 320
21925 310
21926 290
21927 290
21928 300
21929 300
21930 300
21931 350
21932 350
21933 390
21934 400
end
format %tdnn/dd/CCYY date_funds

And the end result should look like this (in this way I am able to calculate the mean(funds_available) per company_id between IPO_date and announcement_date):

clear
input byte id int(IPO_date announcement_date date_funds funds_available)
1 21915 21921 21915 100
1 21915 21921 21916 100
1 21915 21921 21917 200
1 21915 21921 21918 200
1 21915 21921 21919 250
1 21915 21921 21920 270
1 21915 21921 21921 270
2 21921 21933 21921 270
2 21921 21933 21922 320
2 21921 21933 21923 380
2 21921 21933 21924 320
2 21921 21933 21925 310
2 21921 21933 21926 290
2 21921 21933 21927 290
2 21921 21933 21928 300
2 21921 21933 21929 300
2 21921 21933 21930 300
2 21921 21933 21931 350
2 21921 21933 21932 350
2 21921 21933 21933 390
3 21927 21934 21927 290
3 21927 21934 21928 300
3 21927 21934 21929 300
3 21927 21934 21930 300
3 21927 21934 21931 350
3 21927 21934 21932 350
3 21927 21934 21933 390
3 21927 21934 21934 400
end
format %tdnn/dd/CCYY IPO_date
format %tdnn/dd/CCYY announcement_date
format %tdnn/dd/CCYY date_funds

Thank you in advance.

[Nick Cox]

This may help.

clear
input int(date_funds funds_available)
21915 100
21916 100
21917 200
21918 200
21919 250
21920 270
21921 270
21922 320
21923 380
21924 320
21925 310
21926 290
21927 290
21928 300
21929 300
21930 300
21931 350
21932 350
21933 390
21934 400
end
format %tdnn/dd/CCYY date_funds

save available 

clear
input byte id int(IPO_date announcement_date)
1 21915 21921
2 21921 21933
3 21927 21934
end
format %tdnn/dd/CCYY IPO_date
format %tdnn/dd/CCYY announcement_date

gen toexpand = announcement_date - IPO_date + 1 
expand toexpand 

bysort id : gen date_funds = IPO_date + _n - 1 

merge m:1 date_funds using available 

sort id date_funds 

list, sepby(id)

     +---------------------------------------------------------------------------+
     | id    IPO_date   announc~e   toexpand   date_f~s   funds_~e        _merge |
     |---------------------------------------------------------------------------|
  1. |  1    1/1/2020    1/7/2020          7      21915        100   Matched (3) |
  2. |  1    1/1/2020    1/7/2020          7      21916        100   Matched (3) |
  3. |  1    1/1/2020    1/7/2020          7      21917        200   Matched (3) |
  4. |  1    1/1/2020    1/7/2020          7      21918        200   Matched (3) |
  5. |  1    1/1/2020    1/7/2020          7      21919        250   Matched (3) |
  6. |  1    1/1/2020    1/7/2020          7      21920        270   Matched (3) |
  7. |  1    1/1/2020    1/7/2020          7      21921        270   Matched (3) |
     |---------------------------------------------------------------------------|
  8. |  2    1/7/2020   1/19/2020         13      21921        270   Matched (3) |
  9. |  2    1/7/2020   1/19/2020         13      21922        320   Matched (3) |
 10. |  2    1/7/2020   1/19/2020         13      21923        380   Matched (3) |
 11. |  2    1/7/2020   1/19/2020         13      21924        320   Matched (3) |
 12. |  2    1/7/2020   1/19/2020         13      21925        310   Matched (3) |
 13. |  2    1/7/2020   1/19/2020         13      21926        290   Matched (3) |
 14. |  2    1/7/2020   1/19/2020         13      21927        290   Matched (3) |
 15. |  2    1/7/2020   1/19/2020         13      21928        300   Matched (3) |
 16. |  2    1/7/2020   1/19/2020         13      21929        300   Matched (3) |
 17. |  2    1/7/2020   1/19/2020         13      21930        300   Matched (3) |
 18. |  2    1/7/2020   1/19/2020         13      21931        350   Matched (3) |
 19. |  2    1/7/2020   1/19/2020         13      21932        350   Matched (3) |
 20. |  2    1/7/2020   1/19/2020         13      21933        390   Matched (3) |
     |---------------------------------------------------------------------------|
 21. |  3   1/13/2020   1/20/2020          8      21927        290   Matched (3) |
 22. |  3   1/13/2020   1/20/2020          8      21928        300   Matched (3) |
 23. |  3   1/13/2020   1/20/2020          8      21929        300   Matched (3) |
 24. |  3   1/13/2020   1/20/2020          8      21930        300   Matched (3) |
 25. |  3   1/13/2020   1/20/2020          8      21931        350   Matched (3) |
 26. |  3   1/13/2020   1/20/2020          8      21932        350   Matched (3) |
 27. |  3   1/13/2020   1/20/2020          8      21933        390   Matched (3) |
 28. |  3   1/13/2020   1/20/2020          8      21934        400   Matched (3) |
     +---------------------------------------------------------------------------+