I have an excel spreadsheet with a set of criteria for deriving a cost on a trade, so if the Region is X and the Date is Y then the cost of the trade is Z. Which is easy enough because I can just join my trades data on the cost data on the relevant fields. However, there are two fields, product and sector, which come in the format: "All except NDFs" or, "All except Banks and Financials".
Ideally we would just modify the XLS such that we would make an entry for every product except NDFs or every sector except Banks and Financials. The trouble is that we are very frequently updating the number of products and sectors, but extremely rarely updating the cost table, so it actually makes it very inefficient to do it this way, as we would have to constantly be updating the spreadsheet with new rows.
At the moment the way I'm trying to go about it is to load in the trade data and then join in the cost data, with multiple if statements to 'parse' the text entries and conditionally match. The trouble is to do this, I have to load in all the trade and cost data and match on the simple rows, then reload the entire data set conditionally matching on the two logic rows. Is there a smarter way to go about this?
