Relational Database (Data modeling)

To me, as I stated earlier today, your setup for the holdings table is incorrect.  You should not be linking to the Maturity, Ratings, and Asset classes.

There should be a child table of indicies, with one record for each of the possible combinations of Maturities, Ratings, and Asset Classes might have.

 Your holding would then link to that.   Unlike Pat, I don't see a M to M relationship.   But what your modeling is un-clear to me, so I might be off base.

Jim.

Let's start with the Maturity Bucket value; is that a fact about a holding, or a fact about an index?

Jim.

Hi Jim,

Thanks for your feedback and I tried to give your comments from this AM a shot but I must be missing something.

The Maturity Bucket value is a fact about a holding and each one will have a bucket value.

For the Index, the Maturity Bucket value could be a direct match (Index1 Maturity Bucket = One Maturity Bucket), a combination of bucket values (Index2 Maturity Bucket = Two or more Maturity Buckets) or encompass all of the values (Index 3 Maturity Bucket = All Maturity Buckets). I hope that makes sense...Thanks!

OK, then that is what I suggested this morning.   Let me double check one more thing: a holding can have one and only one Asset class and rating as well?

and a index can have one or more of those as well?

Jim.

Yes, holdings can all only have 1 Asset Class and Rating while an Index could have multiple or encompass all of the values for ratings but will only have 1 asset class.

OK and one step further; a holding can be tied to one index or multiple?

Jim.

and BTW, it would be nice if you could upload a DB with just the table(s) involved (empty of course).

You can do that easily by:

1. Create a new blank DB.
2. Use the Get External Data function.   When you use that, look for the Advanced or Options button (Not sure which version you have).

3. One of the options will be "Structure Only".
4. Execute the import.

  You'll have a DB with just the table design then and only the tables that you've included.  

  I can then re-do the tables and the relationships so it's easy for you to see how they should be.

Jim.

Typically just one index however that is determined by the portfolio code which essentially has a 1-M relationship with each holding. The portfolio code however has a 1-M relationship with the Indices

Ok, I will upload them shortly.

Thanks Jim!

Here is the DB with the tables..
Jim.accdb

OK, bear with me for a moment, but I want to make sure I get this right.   I'm un-clear exactly how holdings and indicies work together.   I've stripped it down to this for the moment:


Let me see if I got this so far.

1. It all starts with the portfolio.
2. Each portfolio carries a set of mandates that it will be made up of/compared to specific indicies on a weighted value.
3. Indicies are various financial indexes, like the DOW or S&P 500.
4. We are not concerned about what individual holdings those indicies are made up of.
5. Our portfolio is made up of specific holdings, which we do want to know.

 If I'm good on all that, then what I need to understand is why are you trying to connect holdings and indicies?  Is statement #4 above correct?

Jim.

You are pretty much spot on except for #4...But I am concerned with which holdings map to specific indices that is why I need to use the characteristics of the holdings to map to the correct index which ties into your statement #2.

Take for example a portfolio that has 4 mandates/indices all weighted at 25% each. If the individual holdings are not correctly mapped to each index based on their characteristics than we will have large overweight in one and underweights in the others based on the weights of the individual holdings. Secondly, each of the indices have specific characteristics (asset class, maturity bucket, rating bucket) that corresponds to similar holdings. If I have an Index that is an asset class of 1, ratings buckets 4-5 and maturity buckets of 1-3 I would want those holdings that match those characteristics in the portfolio mapped to that index. Let me know if that is not clear and I will show you some sample data that may help. Thanks!!

<<But I am concerned with which holdings map to specific indices that is why I need to use the characteristics of the holdings to map to the correct index which ties into your statement #2. >>

  OK.  And that may be more than one index?   So a holding like Microsoft might be part of the DOW and the S&P 500?

<<If I have an Index that is an asset class of 1, ratings buckets 4-5 and maturity buckets of 1-3 I would want those holdings that match those characteristics in the portfolio mapped to that index. Let me know if that is not clear and I will show you some sample data that may help. >>

  OK, but that seems to be a lookup rather then a relationship.   The reason being is that the asset class, rating, and maturity of a holding are seperate facts and pertain to the holding itself.  

  Indexes also have the same measures, but they are facts about the index, not the holding.   But the two match up.

  To say that a bit differently, if there was a relationship, you would not have asset class, rating, and maturity in the holdings table.   Instead, you would point to the index that would have those characteristics.

 But I'm still hung up on one point however.  By mandate, you are already pointing to one or more indicies, but you are not including asset class, rating, or maturity bucket as part of that.  This goes to your statement:

<<If the individual holdings are not correctly mapped to each index based on their characteristics than we will have large overweight in one and underweights in the others based on the weights of the individual holdings.>>

  Seems like the mandate table is not correct, or I'm not understanding something.  

  Is a mandate something different then this match up of holdings to indexes?  For example, I have four mandates for a protfolio of 25% each, but my holdings in that portfolio may match up based on asset class, rating, and maturity to any number of indexes, not just the four that I have a mandate for?

  A actual example might be a good idea at this point as I'm not quite sure where the problem with the model lies.  To do a relational design properly, you need to understand what's being modeled.

  I'll also add this in quickly, which may help you understand a bit about modeling

  When your constructing your tables, each table needs to represent one specific thing and only one thing.    The columns of the table (which are attributes or facts) should only apply to that thing.  In short, data only gets stored in one place when you do this.

  If you use actual data and start to fill in your tables, if you see:

1. Data that repeats row after row:

CustID   Cust Name   Holding
1             Jim                MSFT
1             Jim                NYVF
1             Jim                BSHK

 Then your have more then one thing being described by your table.

  Here  we have a list of holdings and everything in this table should relate only to a holding.   But the cust name is the same row after row.  So it's not a fact about a holding and belongs to some other "thing" (a customer).

What we should have is:

tblCustomers
Cust ID    Cust Name
1              Jim

tblCustomerHoldings
HoldingID    CustID    Holding
1                   1            MSFT
2                   1            NYVF
3                   1            BSHK

  Now the fact "Cust Name" is in the right place.  As you can see, it is now only stored in one place.


2. You have repeating fields:

CustID   Cust Name   Phone#1  Phone#2  Phone#3

  You need a child table.  The Phone#'s are a fact about a method of contact that belongs to a customer, but are not a fact about a customer (like name).

What we should have is:

tblCustomers
CustID
CustName

tblCustomerContacts
ContactID
CustID
PhoneNumber

and we'd end up with three records in the child table.  Phone# doesn't appear in the customer table.

3. You have fields that apply to some records and not others. Say you had a table of "objects" and we had a pen and a car.  So you do:

Object ID      Year  Make  Model     Mfg      VIN     Ink Color
  1                1973   FORD  TARUS    FORD   12345    NULL
  2                1974  PILOT   MICRO  PILOT    NULL     Blue

  This is a tip-off that your describing more then one "thing" in your table.    Every field (fact) in your table should apply to the "thing" the table describes.  Here we can't do that.   A pen doesn't have a VIN, and a car doesn't have an Ink Color.  We can never fill in a value for those records because it simply doesn't apply.   We need two sperate tables.

  So when testing your design with actual data, if you look for these three simple "tip-offs", you'll know if your design is correct or not.

  So let's try working with some actual data and then this might make more sense to me and I can figure out where we need to go.

Jim.

Hi Jim,

Thanks for the feedback it is very helpful. I have attached some sample data that has been scrubbed. Here is some additional background that may help as you look through the data...

A portfolio may have 1 or more indices and all holdings within a portfolio must be mapped to a particular index and cannot belong to more than 1 index

There may be many of the same holdings that belong to different portfolios and thus could have the same or different indices depending on the portfolio.

thanks again for your help!

I have attached some sample data that has been scrubbed.

I don't see any attachments.

sorry I thought I attached it...
New-Jim.accdb

<<A portfolio may have 1 or more indices and all holdings within a portfolio must be mapped to a particular index and cannot belong to more than 1 index>>

 OK, got that.   But is this "mapping" restricted to the the index's specified by the mandates, or are the mandates someone totally seperate?

From my earlier comment:

"Is a mandate something different then this match up of holdings to indexes?  For example, I have four mandates for a protfolio of 25% each, but my holdings in that portfolio may match up based on asset class, rating, and maturity to any number of indexes, not just the four that I have a mandate for? "

 I can't figure that out from your sample data.

Also in the sample data, you have table indicies with a field if "Index_Name"; is that supposed to be unique within the table?

Or is it the combination of Index_Name, Class1, Class2, Index_Mat_Bucket_Number, and Index_Rating_Bucket_Number that would form a unqiue combination?

What I'm asking is, when you create a mandate, does that simply indicate the index, or the index along with a specific class, Maturity, and Rating combination.

 Earlier you said that an index could only have one class, but might represent multiple ratings and maturity buckets.

Jim.

Hi Jim-

Sorry for the delayed response.

In regards to your first question. yes, the mapping is restricted to the indices specificied by the mandates.  In regards to your second question on index names they will be unique but I think the primary key should be a number as the descriptive tag could possibly change over time. And to answer your third question. yes, a mandate simply states the indices and the weights assigned to each index.

Thanks!

<<Sorry for the delayed response. >>

Busy here too!

 I'll try and get something back to you in a bit, but it may fall into tomorrow.

 Sorry this is dragging out like this...

Jim.

No problem, this may turn out to be something I have to go back further and analyze the business logic and look for some possible data restructuring. I do appreciate your comments and responses they have given me a good way to break the problem down and possibly figure out another approach.

Hey Jim,

So I have taken into account your comments and restructured my DB tables a bit more so that for each characteristic I am looking to assign an Index to I have a table that establishes the one-to-many relationship. However, what i am running into is that when I go to query the holdings data I get values that do not match up to the original data values and position counts. For example, if I have a portfolio that should have 100 positions and a market value of $1,000 when I attempt to query the data matched with the indices I get more values and positions than I would expect and I am not sure how this is happening if I have these pre-established relationships. I've attached another DB with the updated relationships and tables (empty)....maybe I am just not setting up the relatiopnships correctly? I would have thought with these relationships that each position would then be assigned to one index based off the mandate/portfolio.

Thanks!
Jim-New.accdb

I am so sorry...I totally let this slip.  Let me look at what you have.

Jim.

It looks a lot better then it did and I don't think your too far off, but I'm still somewhat trying to make heads or tails of this.  I just don't know the business enough to tell what's right and wrong and without any data, I can't make sense of it.

So here's my suggestion;  start with the portfolio table and tblMandates in a query.   Does that give you the data you expect?  Now add in tblIndicies; still get the correct data?

Now start a new query again starting with portfolios and add in tblHoldingsAndAnalytics.   Again check data, then go for adding in tblSecurityData.

Let me know what you find.

Jim.

Hey Jim,

No problem...I was spending time reformatting the data so there wasn't anything on your end I needed until I reconfigured it all. In terms of your query steps:

1. Portfolios and Mandates

I got the correct data: Portfolios with all listed mandates and weights

2. Portfolios, Mandates and Indices

Again, I got the correct data as the Indices matchup 1-1 with each mandate per portfolio

3. Mandates, Portfolio, Indices and Holdings/Analytics Data

Here is where I am getting data that doesn't make sense...For instance, in one portfolio I would expect to get say 100 records of holdings. however, when I use a query that has the tblPortfolios, tblMandates and tblHoldingsAndAnalytics I get a multiple of the 100 records I expected. Looking at the query it looks like I am getting duplicates of the security ID....Interesting though is I just tried a query that has tblPortfolios, tblSecurityData & tblHoldingsAndAnalytics and I get the correct number of records. Do you know why that might be happening? I feel like we are getting pretty close and I do appreciate all your insight and help!

Thanks!

<<Mandates, Portfolio, Indices and Holdings/Analytics Data>>

<<Here is where I am getting data that doesn't make sense...For instance, in one portfolio I would expect to get say 100 records of holdings. however, when I use a query that has the tblPortfolios, tblMandates and tblHoldingsAndAnalytics I get a multiple of the 100 records I expected.>>

 and you would.  You'd have 100 records for each mandate.  

 The better question is, why are you trying to do this?  To me, they seem to be two different things.

 Thinking of the queries you just did, picture a form/report like this:

 Protfolio:
      Code:   JRD
       Name: Jim Dettman

     ---------------
     Mandates:
          Effective date  mandate weight    IndexID    Index Name
     ---------------
      Holdings:
          Holding date    Market_val   SecurityID   CUSIP


  Protfolio:
     Code:  FG2014
     Name: Finance Guy

     ---------------
     Mandates:
          Effective date  mandate weight    IndexID    Index Name
     ---------------
      Holdings:
          Holding date    Market_val   SecurityID   CUSIP

 So a "main" form/report which has the portfolio info, then two sub form/reports, one lists the mandates and one lists the holdings.

 Now, what question are you trying to ask that doesn't fit that?  For example, do you want to know for the mandate which holdings are part of that?

 I need to re-read the thread too to refresh my memory and where you were trying to go with this.

Jim.

Yes, your illustration of the report is pretty close to what I am trying to accomplish in terms of which holdings belong to which mandate and their corresponding index.

As another avenue what I am going to do if I can't get this to work in Access is simply create a composite number in Excel that concatenates the Asset_Class_Number, Maturity_Bucket_Number and Ratings_Bucket_Number for each Holding and to the same concatenated composite for each Index (knowing that each index may have several combinations of various maturity/rating bucket numbers) and then run a VLOOKUP based off those values....hopefully that might give you more insight to the end result I am looking for.

Thanks!

OK, now were getting down to it.   The illustration I showed was three seperate things:

Here's the protfolio,
       Here's the mandates
       Here's the holdings

What your looking for is:

Here's the portfolio
       Here's the mandates
              Here's the holdings that apply to this mandate.

If that's correct, then there is no direct relationship.  I need to re-read through the thread, but what you asking is, which holdings fit under a given mandate, based on the indicies it has, where the holding has a maturity bucket, rating, and asset class that match.

This from your last comment:

<<As another avenue what I am going to do if I can't get this to work in Access is simply create a composite number in Excel that concatenates the Asset_Class_Number, Maturity_Bucket_Number and Ratings_Bucket_Number for each Holding and to the same concatenated composite for each Index (knowing that each index may have several combinations of various maturity/rating bucket numbers) >>

 Reflects that.

Jim.

yes, I would like to have the each holding in the portfolio in the end matched up to an index. This is conditional on the characteristics of the index. Thus, if I have 5 positions in a portfolio each one will fall into 1 index based on its matching characteristics with the indices for a chosen mandate.

I was attempting with my DB to be able to create those relationships so as new holdings come into the portfolio it will be easier to map them to various indices based off their characteristics rather than having to always update formulas in Excel. Excel is good for various things but I think it easier to maintain data integrity in a DB...

thanks!

<<yes, I would like to have the each holding in the portfolio in the end matched up to an index. This is conditional on the characteristics of the index. Thus, if I have 5 positions in a portfolio each one will fall into 1 index based on its matching characteristics with the indices for a chosen mandate.>>

 OK.  And I know you've said that already, but now we come down to the rub (and again, you may have already answered this, and if so, sorry).

 Can I have a holding that fits more then one index?   Can I have more then one index that might suffice for a mandate?

 This goes to the point of who does the maturity, rating, and asset class "facts" (or data) belong to; the mandate, the index, the holding, or even possibly each.

 It sounds to me like your there already with one small change.  What your looking to do is not a direct relationship, but rather simply a lookup and that's why your having a problem.

 Walking through it, I work with a portfolio and go to create a new madate.   I choose an index based on the combination of maturity, rating, and asset class.

 Now I'm presented with a list of holdings that match this index's attributes and can choose anyone, and I can choose one or more to add to the portfolios holdings.

 That's the point I'm stumbling at.  When I think of an index, it's made up of a pre-determined set of securities (like the S&P 500), not that I can choose which securities belong to a index.

 I think we just need a linking table and then we'll be done.

Jim.

Can I have a holding that fits more then one index?

In theory, there could be however, I have the index characteristics defined in such a precise manner that a holding will fall into one of them and be mutually exclusive in fitting another index (this is a an update to what I had originally stated in my first post so with this rule I think it makes more sense)

Can I have more then one index that might suffice for a mandate?

Yes, there could be multiple indices per mandate but his also ensures that each holding will fall into a particular index per my statement above

Now I'm presented with a list of holdings that match this index's attributes and can choose anyone, and I can choose one or more to add to the portfolios holdings.

yes, this is exactly what I am looking to do...

 

That's the point I'm stumbling at.  When I think of an index, it's made up of a pre-determined set of securities (like the S&P 500), not that I can choose which securities belong to a index.

You are correct in your assertion of indices. However, what i am trying to do is compare holdings data to these various indices. But in order to match them up properly I need to utilize the characteristics of the securities of themselves and the index.

For example, say you wanted to compare a stock portfolio to the S&P Growth Index and S&P Value Index. To keep it simple let's say that 100% of the portfolio is invested in Tech stocks of various sizes and market multiples. Some stocks in the portfolio may belong to the S&P index however there are others that don't. If you wanted to compare the portfolio's tech stocks to an index you would need to use the characteristics of each position (P/E, Sales Growth, etc) to determine which index it should be compared to. So if I had a Microsoft (low P/E) and a small-cap tech stock (high P/E) in my portfolio I would want to compare them to different indices (Value/Growth respectively). now microsoft is in the index but the the small-cap stock is not. however, I want to see how its charateristics compare to a universe of securities (Growth Index) with similar characteristics. Hopefully this example clears it up a bit...in the end that is all I want to do is compare a holding to a summarized values of a pre-determined universe of securities(Index)

All right, I've re-read the whole thread a couple of times, gone back and looked at the sample data, and I still have a number of questions and am still un-clear on how it needs to be structured.

 In order to make some progress on this, what I'd like to do is take it one step at a time using some sample data as we go along.  If the sample data doesn't make sense to you, please correct it so it does.   As we do each step, I'll come back with another relationship diagram and we won't move on till were sure it works.

 Let's see if we can't do this rapid fire and get this tied off today.

Once again, I've stripped things down to what I believe is the basics. Please have a look at the following:


 We've got securities, which have a maturity, rating, asset class and other attributes.  We have portfolios, made up of holdings (tblHoldingsAndAnalytics) which are securities.   Portfolios have mandates as well.   We also have indexes

 Let's talk about the indexes first.

 An index can have multiple values for maturity, rating, and asset class.   So I can have:

Index:A

Maturity      Rating       Asset Class
      1                1                   1
      1                1                   2
      2                1                   1
      2                1                   2
      3                2                   1
      4                7                   1
      4                8                   2

 Is this correct?    

Jim.
Region-Capture.jpg

Hi Jim,

Yes that is correct....indices can have multiple values for each attribute.

I was able to get something to work  in the meantime by essentially using Update Queries for the holdings and assigning numeric values for each attribute which are based on the corresonding values in Attribute Table (Rating, Maturity, Asset Class, etc). I then created a composite value for each holding based on its various attribute numbers.

I also assigned all combinations of each attribute for every indice which I used to create composite number as well. After I just used a join in a query based on the composite number. It seems to working well but I would be interested to hear your feedback or possibly see where we could improve it.

Thanks!

All right, now take a look at what I've done:


I've added tblIndexAttrbiutes.   Each combination of index, Asset class, maturity bucket, and rating bucket must be unique.  An index can have one of more of these combinations.

Now let's move onto the mandates.  When I specify a mandate, will all the indexes associated with it have the same asset class, maturity bucket and rating bucket, or can they be different?

Jim.

Hi Jim,

sorry for the delay...was out of the office for a few days. In terms of your question, each mandate could have different asset classes, maturity buckets and ratings buckets. In fact, that would be the norm vs the exception.

Thanks!

OK, now take a look at what I've done:


I've added tblMandateIndicies, which links a mandate to one or more indexes with a set of attributes.

Now for the final hurdle, holdings.

1. Must a holding fit within a mandate or can it just be a holding within the portfolio?

2. Can a holding apply to only one mandate, or can apply to more then one?

3. Within a mandate, does a holding apply to an index with a set of specific attributes?

4. If yes on #3, can it apply to more then one?

Jim.

Hi Jim,

Must a holding fit within a mandate or can it just be a holding within the portfolio?

A holding will only belong to a portfolio and mandates will cover portfolios. From a hierarchy standpoint (lowest to highest) it looks like:

Holding --> Portfolio --> Mandate

Can a holding apply to only one mandate, or can apply to more then one

For a single portfolio, then yes, one holding will only fall into 1 mandate...however, I could have 2 holdings which are identical but are held in 2 different portfolios which thus could have 2 different mandates. For the most part they may fall into a the same/similar mandates/indices due to their characterisitics but one mandate may be more precisely defined than the other thus the holdings may map to a different indice

Within a mandate, does a holding apply to an index with a set of specific attributes

Yes the holding applies to an index based on its attributes should end up mapping to just one index

Thanks!

Sorry this dragged out as long as it has and I feel a bit bad that we've stopped here because I'm not really sure you have an answer.  

 Unfortunately your business is pretty complex, and my preconceptions about finance and how it works probably got in the way.

 There are times when a forum based approach doesn't work well, and I this is one of them.   I should have jumped on the phone with you and walked through it.

Hopefully though I've gotten you a little further along the road and you don't feel your time was wasted.

Jim.

Absolutely, your help was very much appreciated. With the last derivation that is very helpful and I can build a solid foundation and working process from it. Yes, unfortunately finance can become a bit more complex - especially with the business logic overlapped with data. Thanks again Jim for all your help!