# Bloomberg API / Excel Add In - Delisted Stocks

So I am using the Bloomberg Excel add in but can also use the API. My dilemma is I have a list of tickers containing SP500 historical constituents and I have retrieved 600 of them from an alternative source who supply fundamental data such as revenue.

However, for the delisted ones Bloomberg seems the only place that holds fundamentals for delisted tickers, but the problem is how do I feed it the tickers I am looking for, firstly the tickers are structured as so 'PBG-201002' so ticker and year it delisted.

But Bloomberg doesn't understand this so I am wondering how I can translate this into Bloomberg, fairly new to it too so any help will go a long way. :)

• What is pbg? I doubt you will get that directly with the API. As much as I know BBG does not have a ticker syntax where you have a year of delisting. If you have the isin, or similar identifiers you can use that directly. BBG has 24/7 support and should be way better equipped to answer this.in terms of excel and API, there is no difference (apart from some things that only work in Excel) as it's all based on the same c** api. – AKdemy Jun 10 at 19:45
• Perhaps this helps quant.stackexchange.com/questions/42264/… – Enrico Schumann Jun 11 at 7:11
• @AKdemy PBG is just an example, so for instance 'PBG US Equity' would return the latest company using that ticker. However, say a company 10 years ago used this ticker, I want to be able to find and extract their data. But after speaking with Bloomberg they had told me that once a ticker is re-used their system generates a random number that is assigned to the ticker, so it goes from once being 'PBG US Equity' to something like '27800987 US' – RHH Jun 11 at 7:44
• @EnricoSchumann This looks like something I could definitely work with, rather than searching each company name one by one on the terminal – RHH Jun 11 at 7:46
• @EnricoSchumann I know that's in R and I only know Python, but is the general gist of it that you input the ticker and a date of when it would of been listed under that ticker and it should return the Bloombergs randomly generated new ticker for it? – RHH Jun 11 at 7:49

The comment of Enrico definitely works. The APIs are all the same (R is actually not a BBG solution but someone made the C++ API - which is also what excel is based on - usable within R).

=BDP("DRIV US Equity","HISTORICAL_ID_POINT_TIME","HISTORICAL_ID_TM_RANGE_START_DT = 20130101") for the excel equivalent. Python uses the same overrides (BLPAPI) as found on WAPI.

If you had the ISIN, you could easily just use that (or BBG FIGI "ID_BB_Global" but this is less likely to get). =BDP("US25388B1044 Isin","Ticker") for example which is the ISIN for the "old" DRIV US which shows the "new" ticker 161136D. You do not need the ticker, and can get the fundamental data directly via the ISIN.

You can also look it up online where you can enter a FIGI, ticker, company name, other identifier.

• Great thankyou. This is a much easier way than searching each company name on a terminal. I saw that you could use ISIN, CUSIP and others as the input but sadly could not find somewhere which had these available. I did look at FIGI but it returns hundreds of results for a ticker so didn't know which ID to use. Do you know in Excel how I could use that formula as a drag/drop, as I have a list of tickers 'AM US Equity' in a column and then the dates in another '20120801' and was wondering if I could use the formula as a somewhat function. Sadly I do not have Python on this terminal either – RHH Jun 11 at 8:57
• Something like: =BDP("C2","HISTORICAL_ID_POINT_TIME","HISTORICAL_ID_TM_RANGE_START_DT = D2") Whereby C2 and D2 are Excel cells. As I have 400 tickers to do – RHH Jun 11 at 9:04
• =BDP(C2,"HISTORICAL_ID_POINT_TIME","HISTORICAL_ID_TM_RANGE_START_DT",TEXT(D2,"YYYYMMDD")) as the override needs to have the specific format. YYYYMMDD is language specific, would be JJJJMMTT in German for instance. – AKdemy Jun 11 at 9:11
• Legend, thanks! – RHH Jun 11 at 9:12
• It worked, didn't seem to like the last part of the formula, but worked with just "=BDP(C2,"HISTORICAL_ID_POINT_TIME","HISTORICAL_ID_TM_RANGE_START_DT",D2)" – RHH Jun 11 at 10:50