Bloomberg Security List Generator

Assume that I have a list of bonds for various deals. However, I only know, e.g., the ISIN (alternatively security name or deal name) of a particular bond (that is part of a given deal). However, I do not know how many securities belong to a given deal. For example, the "HERME 12 Mtge" deal includes 5 different securities (i.e., ISINs), but I only know one particular ISIN (e.g., XS0271028838) within that deal.

My idea is to use the information (e.g., ISIN or deal name) from that specific bond to get all ISINs or security names within a specific deal. I end up with a list of multiple deals (orange) and should have an additional list of securities within each deal (blue) as shown in the figure below.

I know that in the Bloomberg terminal I can see how many securities are covered within each deal using the Collateral and Structure and VAC View All Classes functions. Since this is very tedious for a large number of deals, I would like to automate this process using the Bloomberg Excel add-in.

In fact, it would be enough to achieve this for one deal, because then I could use a VBA procedure.

One way to achieve this is to use the BQL function, and search the debt universe for any securities that have the MTG_DEAL_NAME matching the deal you are after.

So, a 2-step process,

1. Take the ISIN you have, and get the MTG_DEAL_NAME field via BDP(): =BDP(C2 & " Mtge","MTG_DEAL_NAME")
2. Use BQL to search for any securities that have that deal name: =BQL.Query("get(id_isin()) for(filter(debtUniv('ALL'), mtg_deal_name == '" & C4 & "'))", "showids=false", "showheaders=false","array=true")

NB. I've used the dynamic array version of BQL (array=true), so you can simply pass this list to another function.