<>
Ok, that makes the solution slightly more involved:
******
clear*
input BondID:mylabel str10(Date Time) Bid_Price Ask_Price , auto
AAA 20090729 090540 100.00 .
AAA 20090729 092307 100.05 .
AAA 20090729 093051 . 101.10
AAA 20090729 093523 . 101.20
AAA 20090729 093617 101.05 .
AAA 20090729 094521 . 101.20
AAA 20090729 094654 100.30 .
AAA 20090729 094929 . 100.70
AAA 20090729 100002 . 100.50
end
egen newdate=concat(Date Time)
gen double timeoftrade=clock(newdate, "YMDhms")
format timeoftrade %tc
//get time difference
bys BondID (timeoftrade): /*
*/ gen timediff=timeoftrade[_n]-timeoftrade[_n-1]
//get the spread
//have to check both ways
bys BondID (timeoftrade): /*
*/ gen spread=min(Ask_Price[_n]-Bid_Price[_n-1], /*
*/ Ask_Price[_n-1]-Bid_Price[_n])
//get the minimum time diff
bys BondID (timeoftrade): /*
*/ egen mintime=min(timediff)
//get rid of auxiliaries
drop Date Time newdate
//get desiredspread
bys BondID (timeoftrade): /*
*/ egen desiredspread= /*
*/ max((timediff==mintime)* /*
*/ spread)
list, noobs
******
HTH
Martin
-----Ursprüngliche Nachricht-----
Von: [email protected]
[mailto:[email protected]] Im Auftrag von help me
Gesendet: Donnerstag, 30. Juli 2009 19:33
An: [email protected]
Betreff: Re: st: AW: How to calculate the bid-ask spread in this case?
Dear Martin,
Thank you very much for your answer. Your suggestion is very helpful.
Now I can have a measure of the bid-ask spread!
However, I am afraid I did not correctly explain my problem. The
actual data is more complicated although it has the same structure as
the hypothetical one that I provided.
Bond ID Date Time(HHMMSS) Bid_Price Ask_Price
AAA 20090729 090540 100.00
AAA 20090729 092307 100.05
AAA 20090729 093051 101.10
AAA 20090729 093523 101.20
AAA 20090729 093617 101.05
AAA 20090729 094521 101.20
AAA 20090729 094654 100.30
AAA 20090729 094929 100.70
AAA 20090729 100002 100.50
.
.
.
In this case I want to calculate the difference between the ask price
at 09:35:23 (101.20) and the bid price at 09:36:17(101.05) because
these bid and ask transactions take place at the closest time among
other pairs.
Do you have any idea how to do that?
Thank you.
JHS
On Thu, Jul 30, 2009 at 6:31 PM, Martin Weiss<[email protected]> wrote:
>
> <>
>
>
> This code is very specific to your example data, so if your problem is
more
> general, let the list know...
>
>
> ******
> clear*
>
> input str5 BondID Date Time Bid_Price Ask_Price
> AAA 20090729 090540 100.00 .
> AAA 20090729 092307 100.05 .
> AAA 20090729 093051 . 101.10
> AAA 20090729 093523 . 101.20
> end
>
> compress
> //make sure dataset sorted
> sort Date Time
> list, noobs
>
> collapse (lastnm) Bid_Price/*
> */ (firstnm) Ask_Price, by(BondID)
> gen spread= Ask_Price- Bid_Price
> l
> ******
>
>
> HTH
> Martin
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
*
* For searches and help try:
* http://www.stata.com/help.cgi?search
* http://www.stata.com/support/statalist/faq
* http://www.ats.ucla.edu/stat/stata/
