# AW: st: AW: How to calculate the bid-ask spread in this case?

 From "Martin Weiss" To Subject AW: st: AW: How to calculate the bid-ask spread in this case? Date Thu, 30 Jul 2009 23:14:42 -0400

```<>

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")

//get time difference
bys BondID (timeoftrade): /*

//have to check both ways
bys BondID (timeoftrade): /*

//get the minimum time diff
bys BondID (timeoftrade): /*
*/ egen mintime=min(timediff)

//get rid of auxiliaries
drop Date Time newdate

bys BondID (timeoftrade): /*
*/ egen desiredspread= /*
*/ max((timediff==mintime)* /*

list, noobs
******

HTH
Martin

-----Ursprüngliche Nachricht-----
Von: owner-statalist@hsphsun2.harvard.edu
[mailto:owner-statalist@hsphsun2.harvard.edu] Im Auftrag von help me
Gesendet: Donnerstag, 30. Juli 2009 19:33
An: statalist@hsphsun2.harvard.edu
Betreff: Re: st: AW: How to calculate the bid-ask spread in this case?

Dear Martin,

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<martin.weiss1@gmx.de> 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)
> 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/
```