index/match based on filtered list

Sure there is, could you upload an example so that we can have a look. One way is to combine index with aggregate(15,6 ...) matching specific row items with EUR in a column.

Do you happen to use Boomberg btw? I've worked quite extensively with this kind of data while in DCM before.

Cheers

Yes I do. Could you be more specific as to how I can aggregate the data? I've attached a small section of my model as it is quite a large file. Thanks.

Example.xlsx

P.S I've also pasted values of the Bloomberg formula in case you don't have Bloomberg.

Thanks. Happy to help - will take a look tonight, on vacation and away from a computer until tonight

Hi, quick solution, try the attached:

Row\Col	K
7	=AGGREGATE(4,5,$I$14:$I$24)
8	=AGGREGATE(5,5,$S$14:$S$24)
9	=AGGREGATE(5,5,$L$14:$L$24)
10	=AGGREGATE(4,5,$U$14:$U$24)

Added a filter to the data (that you generate from BBERG) and changed the cells K7:K10 (highlighted in red) using the aggregate function for min and max, ignoring hidden values.

One thing to consider when dealing with BBERG, the dates will usually be string values from a BDP function, so for e.g. cell F14 you can try something like =--BDP($B14&" Corp",NXT_CALL_DT"), i.e. add "--" to convert it to a date directly so you can better use it with the filter.

See how that works for you.

Cheers,
berlan

Hi Berlan,

I don't seem to have "aggregate" function on my excel so everything appears as ?NAME when I change the filter.

I've been working on a more user-friendly filter because my team will be using it on a daily basis so it would be ideal for them to not having to edit the formulae when they want to apply a filter.

I've been using this {=MAX(IF($E$15:$E$100=E14,$I$15:$I$100,MAX($I$15:$I$100)))} for K7 and it works. (E14 is a dropbox with all the currencies) However, I can't seem to apply multiple filters using this method i.e All EUR BBB bonds.

Any ideas?

Hi Johan,

I can't seem to reply to your private message for some reason. But thanks that's exactly what's I'm looking for! One quick question: is there any way to filter out bonds based on their call dates? i.e bonds with call dates >2yrs away.

I'm currently working a on relative value model for hybrid valuation and make it as automated as possible for my team. It's quite a challenge as I'm trying to make it VBA free. Where are you working now?

Sure, certainly! You could do a filter on call dates from 2 years from today's date (like >=8/6/2015), or make a column with years to call. For hybrids with call dates, you could calculate it yourself with the difference from NXT_CALL_DT and Btoday(), a non-volatile function instead of Excel's built-in function today() which is volatile, or there may be a field for that like where you have to check the overrides if any.

The importance is that the date cells are recognized as date/numbers and not strings, i.e. use:

=--BDP($B14&" Corp",NXT_CALL_DT")

instead of

=BDP($B14&" Corp",NXT_CALL_DT")

provided that your settings in BBERG match the regional settings on your computer; you may need to change your Windows time settings to US if it doesn't work with UK settings (which it did for me in London but not in Stockholm). See what works for you.

There are plenty of cool things to do, and to make this model robust and easy to use. Happy to help if you have any formula questions or otherwise.

Cheers,

Johan