DataStream: Matching bond pricing data with the issuing company financials?

daleholborow

daleholborow

Joined
7/17/08
Messages
307
Points
38
Hi all

Does anyone here use DataStream, in particular matching bond issue data with the financial data of the parent company? I have actually seen several people about this, and even called the Thomson DataStream help centre, and have now had their help-desk tell me that there is NO WAY to easily match a bond by its DS code with the issuing company's DS code??? This seems to me to be totally ridiculous???

I'm currently trying to retrieve information about:

a) fixed coupon, bullet bonds that have been issued since 1997 (including coupon rate, maturity, issue date, etc, from corporate issuers not including finance or utility firms). I'd prefer the UK or a Euro aggregate, but might have to end up using American data for this study, the UK market appears to be TINY by comparison...

b) The time series info about the prices those bonds were listed at each day, and finally (and where I am coming unstuck)

c) Retrieve the time series of financial data for the issuing companies (book value of debt, total asset value etc), so as to be able to run some empirical testing of structural bond pricing models.

The problem I have is that I can search for and export into excel a list of bonds matching my criteria. The export includes the NAME of the company issuing the bond, but not a reference CODE... so when it comes to trying to retrieve the relevant company data, I have to then manually and individually search for the company by name (which only seems to find matches very rarely) and THEN if I can find a match, try to download the financial data separately.
Given that I hope to run my tests over a sample of 100-200+ bonds at the very least, this will take me days just to download the data (our access is also hideously slow for some reason), whereas it would be a matter of seconds to write such a query in sql or something.

Can anyone possibly tell me any way to get out the company code while extracting bond info, to speed up my work? Has anyone else had a similar problem or faced the similar frustration?

Hopefully there is just something simple I've missed, but all my attempts thus far have been a failure and manually looks like my only option
face-icon-small-sad.gif
... just asking around in hope that someone has a simpler method?

Cheers
Dale
 
Can you get a data dump of issuers + codes? Then you can go into excel and do a vlookup to match them up.

Alternatively, you can leverage excel to generate a long boolean query (company = "XYZ" OR company="PQR" ...) and copy and paste -- doesn't solve the fundamental problem, but makes it much, much easier. (I am guessing what the interface is like).

Perhaps you could give more details about what generally you can and can't do with the interface; maybe there's a hacked-up way to automate it.
 
Hi Doug,

The interface is very clunky, so there probably isnt much value in wasting anyones time trying to describe how I use it unfortunately...

I'd considered something along the lines of what you are suggesting (loading into excel or SQL or something), a list of bonds and a list of issuers and matching on them both, but what complicates the issue even more is that the names between the two seem to differ...

By that I mean that say, when exporting the bonds, the issuer name might appear in the list as "W.K. Tekni" whereas when you look through the companies, the best match I might find could be "WunderKinder Tekni Media Group"... in some instances the names are very close and I *might* get lucky with a "lookup where name like BLAH" but in others, its significantly different and would still require a LOT of manual intervention.

Then there is the problem of multiple potential matches, a bond might have been issued by "pepsi incorporated" for example, but when you search for companies with the name "pepsi" there might be "pepsi bottling company, pepsi holding corp, pepsi co of america, pepsi (FRA)" etc.

All in all, its proving to be remarkably counter-intuitive, I'd assumed that being able to match a bond with the parent company would be a default piece of functionality, and yet it doesn't appear... Its even possible to export the details of the underlying stock when looking at convertible bonds, and yet no way to trace the company ?? Very strange.

Thanks for your time though... I might end up having to try something like you suggest to at least take SOME of the legwork out, get a collection of initial matches and then try to add additional items myself.

Cheers
 
Given that, I would say the best thing to do is come up with a few heuristics, e.g. break the name into individual words, search in each word; compare directly; look at underlying stock price... and the item with the most matches is guessed to be the issuer.

Bloomberg or some other data provider isn't available?
 
No Bloomberg... I've looked through it briefly at another location.

I wasn't experienced using it and so the short time I had was spent just trying to figure out how to export the data. While it seemed promising, I unfortunately don't have access to it any more, so I'm stuck with DataStream and a distinct lack of people here know anything about how to use it...

That added to the explicit answer by the DataStream help team and I think I'm going to be writing string matching algorithms and spending some long hours at the terminal... *sigh*
 
For anyone who reads this and is interested, I got a phone call from DataStream this afternoon, and the woman who spoke to me again confirmed that "it is currently not possible to precicely match bonds issued with the issuer.. many clients have asked for such a feature recently, but our technical team advise that it is not currently possible. In the meantime, we can only suggest that you try to match the bonds with the company based on the names provided".

So,
a) I am actually rather impressed that someone called me back, I'd made a call to an account manager and got her answering machine, but presumably she set up the response call, which was a pleasant surprise...

b) I am totally disgusted in their system design team, whoever does their database design needs a swift kick in the pants...

Anyway, what this means in short is that I'm going to have to trawl through hundreds/thousands of bonds and companys by hand to match them up... so be warned if you intend to do anything similar!
 
Is there any other data provider that might give you that information? Do you have the option of getting rid of them?
 
For me, unfortunately no. The only sources of data that I have available are DataStream for the bonds (and some financial data) and compustat, but an american-only set of data in computstat.

The project I am working on, I'd hoped to do a new study using UK data, which turned out to be very minimal, so I thought about a european sample, and then came across the problem of the unmatchable data sets...

I had a look through a bloomberg system, but unfortunately don't have any further access to it, so its strictly manual for me, i'm afraid. Just thought i'd give other people a heads up, since its something you sit there and think "i MUST be daft, because surely this is easily achievable..."
 
Welcome to the world of financial data, Dale
As long as you try to map/append data from one provider to another, you will always have a problem with each company using their own type of name, ID. It's a problem with bonds, stocks, credit, everywhere.
The problem you have is that bond data is from DataStream and company's financial data is from another source. You can't blame DataStream for not able to map their data fields to other providers.
Matter of factly, every firm is to establish their own system of ID as industry standard (example RED code).
You have to work within the system i.e comes up with solution of your own. Matching names are an ongoing task that you have to deal with constantly. I certainly sympathize with you since I have and still been facing similar nuisances.
Once you match a pair, think you are done?
One day, you will wake up and find that the pair is no longer working. The company decides to change its name over night. Companies rename, merge, demerge all the time and it's a fact of life.
Example, on 5/6./08 Credit Suise Group decides to call itself Credit Suisse Group AG, so the ticker becomes CSGAG from CRDSUI
One way you can deal with this is store all the data in an internal database (Access, SQL) and do the matching internally using some script that you write. The pairs that you can't the match, unfortunately, you need to do manually.
I don't work with bonds or Factset exclusively but I have access to bond data at work from different sources. One thing bond has uniquely is ISIN, CUSIP. You can work backward from Factset to see which bonds associated with a specific company and try to match them by CUSIP.
Here is a snipet of the bond XML daily data from MarkIt. Using all the data fields, you should be able to map a bond to its issuer.
Code: 
    <Date>2008-08-07</Date>
    <Contributor>Trace Composite Retail</Contributor>
    <ShortName>FORD Mtr Cr Co LLC</ShortName>
    <Ticker>F-MotCrLLC</Ticker>
    <Coupon>0.0735</Coupon>
    <Maturity>2012-05-15</Maturity>
    <Ccy>USD</Ccy>
    <Tier>SNRFOR</Tier>
    <Depth>3</Depth>    
    <CompositePrice>0.6876666666666668</CompositePrice>    
    <Instrument>F-MotCrLLC 7.35 15May12 BondCall</Instrument>    
    <AvRating>B</AvRating>    
    <ImpliedRating>CCC</ImpliedRating>    
    <Sector>Financials</Sector>    
    <Region>N.Amer</Region>    
    <Country>United States</Country>    
    <Isin>US345402X639</Isin>    
    <Cusip>345402X63</Cusip>    
    <CompositePriceRating>NR</CompositePriceRating>
 
Hi Andy

I've worked in IT for about 5 years now for both private and university systems, so I'm well aware of the difficulties in tying data together when its being retrieved from different sources/systems. But I think my mentioning CompuStat earlier may have confused the issue... In all my previous comments, I meant to indicate that I have access to compustat and datastream, but only DS has european data, and so i am using ONLY datastream for both the bond AND the firm financial data...

DataStream have their internal code for the firm, and an internal code for the bond, and no link between them... I'd understand if I was trying to link bond data from DataStream with say, financial data from CompuStat, but ... *shrugs*

I still think that for DataStream to have financial data for a company, and also the bonds issued by that same company, and have no logical tie between the two, seems remarkable and flawed... a free-floating database design with no relational ties?

And I'd been given the impression that this is one of the industry leading sources of data...

I've never heard of Factset, but thanks for the tip, I'll follow it up and see if that will help me out at all :)

Cheers!
 
I don't have DataStream but if what you said is true, then it's pretty lame. You mean to say when you pull out all the data about a specific bond, there is no issuer info?
How do you access DataStream? Web interface? Local database? Do you have their data schema to see if there exists any remote link between bond data and company data?
 
Andy said:
I don't have DataStream but if what you said is true, then it's pretty lame. You mean to say when you pull out all the data about a specific bond, there is no issuer info?
How do you access DataStream? Web interface? Local database? Do you have their data schema to see if there exists any remote link between bond data and company data?
Click to expand...
Yup, exactly... When I pull out bond info from DS, it has the bond name, coupon, DS code, ISIN number specific to that bond issue etc, and includes some "short" version of the company name that issued the bond, but not a DS-specific code for the issuer. So, when I contacted DS, two different people from their technical staff have now told me that "there is no direct link available to join the bond and the issuer, we can only suggest you try to do a search based on the company name".

Which, as you mentioned earlier, is often a huge pain in the bollocks, because the "short" company name column in the bond export details, is often very different to the "actual" company name which you see when you go and try to find the company in a separate search, making string matching very painful for example.

I access DS through their desktop application Thomson DataStream (V4.0, from memory, I am away from it now), which is pretty much your regular windows-application interface. It makes real-time requests to the Thomson data servers, which actually makes it hideously slow a lot of the time :( Nor do I have any db schematics to look through, the system is actually a bit of a mystery, seems its hardly used by anyone and hence no-one has much hands-on experience. I'm just going by my own experimentation, and the comments from the DS technical staff that I've spoken to over the phone.

I'm pretty much resigned to having to go through with a printout and a big red pen circling matches at this stage, I don't have time (or the cost-benefit payoff) to implement anything more complex, and it would appear that there isnt a simpler way either.

I'd be more than happy for anyone reading this to prove me wrong though? :D
 
Hi Dale, hi all,

I am currently facing exactly the same problem and wonder if you've found a convenient solution to it? Indeed, matching issuer ISINs with bonds outstanding at certain points in time does not seem to work with Datastream, which I find ridculous.
Unfortunately, I have a list of 1,400 companies and a period of five years for which I semianually need to gather data about bonds outstanding. Therefore I'd be glad if you could give me some hints about what to do (or what you have done) about it.

Regards
Tobias
 
Tobias, I ended up exporting a full list of all available companies into excel, and a full list of all the bonds, and after applying some limiting criteria (only companies who ever had two or less bonds on issue, for example), narrowed down my potential pool of bonds significantly.

That meant I then had to go through and manually match the remaining bonds to a company record in my company list. This process was about three times more painful than it sounds, so you're not going to have a good time...

I was unfortunate, in that I had originally intended to automate my analysis over 1000s of bonds/companies, but in the end, I had to do my study on a tiny fraction of that... largely because of the terrible relationships between the data.

If you're doing your work for a university study, you might take some heart in the knowledge that many of the quant-finance research papers that I was focusing on used small numbers of bonds as well. So, perhaps you can adjust your research focus to use a considerably smaller sample set, and make a point of this difficulty as you write up? Of course, that might not be possible, if you're working on something for industry...

That said, a couple different research papers allege that they tested several hundred bond/company combinations, which I can only imagine to being comparable to having my liver torn out each day by an eagle, only to regrow again each morning...

So, in short... I'd suggest calling Datastream help again, asking whether anything has been updated or if they have any new ideas/patches, but I'm not overly optimistic.

Please let me know how you go, and good luck.
 
Dale, thank you for your fast reply. Unfortunately, Datastream only confirmed me that they can't provide a database where issuer isins and bonds are matched. As I 'only' need the data for my thesis I can simply use what I find. But since I'm really into my topic, I'd love to have a good data set. But maybe I will have to give up being a perfectionist in this case...
Anyways, I recently saw that Datastream allows you to export the bond description (including the issuer name) to Excel. But as far as I know it's only for currently issued bonds, not dead ones, which I need. I haven't given up yet and if I find out more, I'll let you know.

Regards
Tobi
 
Hi Tobias and Dale.. I'm in the same position of yours.. I have to simply find out how much of debt of each company of my dataset is bond and how much loan.. it seems quite obvious and straightforward but having access only to Datastream looks impossible. @Tobias, How did you solve your problem? @Dale do you know any other methods? It passed 5 years from this post...Maybe something changed I hope you can help me find a solution... I'll be glad to hear from you two...I'm currently working on my thesis and I'm freaking out.. Sorry for bothering you. THANKS
 
Back
Top Bottom