Individual and group/composite Asset Valuations recorded for different dates.

[Reg Brown]

Hello there,

Complete newbie here to databases and the forum. Using Access 2007. I'm probably an Intermediate/Advanced with Excel, but am now finding that the info I need to retain for the project below is beyond Excel's capability. So I've turned to access. I've looked up an Oracle data model 101 booklet online to get to grips with database nomenclature, looked at the basics of Access, and away I've gone.



I'm looking to put together a database for an investment manager, so that he has a record of clients (CLIENT), who will use one/many investment vehicles (INVESTOR) to make one/many commitments (COMMITMENT) resulting in one/many investments (INVESTMENTS).

The fund Manager has created a few investment funds (FUND) through which assets are held on behalf of Clients/Investors. To date each Fund has one (single vehicle fund) or two (master/feeder fund) corporate vehicles (INVESTEE) through which an Investment is made by the Investor. Each Investee issues one or more classes of equity instrument (UNIT) which, depending on the class, will equate to a unit in the Fund for which that Investee relates (ie each Unit issued by Investee equates to a number of "FundUnits" in a Fund).

Each Investment will always be in one Investee, and will relate to one/many holdings of Units (HOLDING). Each Holding will only relate to one Unit class.

So I've got to the point in creating the database where I can produce a report for each/all Investors showing their Commitments amounts, each Investment amount, and the Holdings within that commitment showing the number of Units x subscription price per Unit for that Holding, giving me the "HoldingCost". I can produce a report where the Data in report is pretty much static [other than when an investment is no longer current, in which case the "End Date" of the investment will be inserted and will be a value lower than "date()"].

Problem: what I want to do is input a given valuation for the Fund (taken as a whole) on a given date (quarterly or monthly valuation dates); lookup the number of FundUnits associated with the Fund on said valuation date; therefore calculate a FundUnit asset value on each value date; which can then be used to value each Holding on each value date (and hence each Investment can be valued).

What I'm really struggling with is converting (what I think are) the necessary Data Model amendments into the appropriate tables/queries in Access.

The Capitalised words used throughout are regular tables in my database.

- What I think I need are the following: (not sure if these are tables or querytables or what):

REFCALENDAR (ValuationDate (PK); Notes). One to Many Relationship with FUNDVALUE and HOLDINGVALUE

FUNDVALUE (FundID (FK); ValuationDate (FK); Value [data input]; AggregateFundUnits [ideally calculated from aggregating the units - NumFundUnits - allocated to the current Holdings relevant to the fund]; ValuePerFundUnit [calculated as = Value divided by NumFundUnits]

HOLDINGVALUE (FundID (FK); ValuationDate (FK); NumFundUnits [from HOLDINGs table]; HoldingValue [calculated as =NumFundunits x ValuePerFundUnit]


Further, I think that REFCALENDAR is a regular table/form, but FUNDVALUE and HOLDING VALUE are querytables. Problem is, I don't know how to

(i) ensure that, in calculating ValuePerFundUnit, AggregateFundunits, NumFundUnits and HoldingValue, my database looks up only the relevant records (ie looks-up Holdings records that relate to the specific Fund and are current as of the valuation date)
(ii) ensure that my database obeys the rules of normalization.

It would be great if I can be given some guidance as to whether I am on the right track, regarding the suggested data model amendments, or if I should look at a better way of completing the task. Happy to post screenshots/the database itself, though I need to think about not disclosing the data within it.

Temporal databases are tricky at the best of times; thought it best to get some advice before I disappear up my own database.

Many thanks and regards - Reg

[rpeare]

Do you have a table that is storing the values of a fund with a start date and termination date (or at the least a start date where you can infer the termination date from the next sequential record by date)?

For instance you should have a table listing all your possible funds
Another table that lists the value of that fund during a specific period (has nothing to do with number of units owned, it's the cost of one unit during the time span)

So

tbl_Funds
fund_id  Fund_name
1        Fund A
2        Fund B
3        Fund C

tbl_FundValuations
FV_ID  Fund_ID  FV_StartDate  FV_EndDate  FV_UnitCost
1      1        1/1/2013      1/31/2013    50.00
2      1        2/1/2013      2/28/2013    52.00
3      1        3/1/2013      3/31/2013    60.00

So now let's say you have customers where you want to track the initial value vs the current value of their fund choices

so let's say you have

tbl_Customers
Cust_ID  Cust_Name  ------> other customer related fields
1        Customer A
2        Customer B

tbl_Customer_Funds
CF_ID  Cust_ID  Fund_ID  CF_PurchaseDate  CF_Quantity ----> other purchase related fields
1      1        1        1/7/2013         100
2      1        1        1/9/2013         50
3      1        1        2/7/2013         100

If I understand what you want to be able to do is to end up with something like (let's assume we're in march of 2013):

Customer    FundOwned  QtyHeld  PurchaseValue  CurrentValue
Customer A  Fund A     250      12,700    15,000

Though looking at your post you don't seem to care about the purchase value.

There are a couple of things you can do to facilitate this.

1. In your queries used to calculate the current value of their funds you'd have to use DLOOKUP or DSUM functions (look at domain functions) to find the correct intial cost something like:

dlookup("[FV_ID]", "tbl_FundValuations", "[Fund_ID] = " & [fund_id] & " AND [FV_StartDate] <= #" & [Date of Report] & "# AND [FV_EndDate] >= #" & [Date of Report] & "#")

where [date of report] would be either a user input value or you can default it to today's date with the date() function.

that would give you the valuation ID and you could pull whatever information from that record you wanted in a subsequent query.

I do NOT favor dlookups used this way, they are very memory intensive and bog down queries/reports (make them take longer to run)

2. The second option is, at the time of data entry, to fill in another field that identifies the fund valuation period for quick retrieval. What I usually do is put an invisible field on the data entry form and when they've chosen the items that help you identify the correct fund valuation item (in this case the fund and the date of the purchase) you can run a piece of code that will fill in the invisible field (whether on a bound or unbound form).

Then when you run any queries you already have a link to the correct valuation item.

[Reg Brown]

Your post is really useful Rpeare.

Responding to your questions/queries:

Firstly, your "tbl_funds" is what I refer to as "FUND", so that's all good.

I intend to have a table (equivalent to your "tblFundValuations") that stores the value of the fund as of a certain date (I called it FUNDVALUE in the original post), but my table was to have a mix of data input [i.e. an input for the value of the Fund (taken as a whole) on a given date] and calculations [i.e. the number of fund units in existence on the same date; also a value-per-unit calc]. It sounds like you are saying I shouldn't have calculated fields in this table, which is fine, but then I'm not sure what form the calculated fields should take.

As is implied by your post, my FUNDVALUE table will have a 1/many relationship with FUND, but also a 1/many relationship with table REFCALENDAR (a table with the valuation dates inputted in it - technically fund values are as of a certain date, rather than a certain period, but it's assumed that a valuation applies from the valuation date until the next valuation date, if that makes sense.)

To be clear, I want to manually input the asset value of the fund taken as a whole, but I want to calculate (not input) the fund-value-per-unit (and separately the value of the Holding), as of a particular date, by looking up the number of units in existence at the time (which I can get from the relevant records in my HOLDINGS table).

In terms of "purchase value", I already have this recorded as "HoldingCost" in the report that I've produced already (I definitely am interested in reporting this information too).

So, In terms of what I'm looking for, the report you showed...

Code:
Customer FundOwned QtyHeld PurchaseValue CurrentValue
Customer A Fund A 250 12,700 15,000
...is fairly close to what I want, though I want the "CurrentValue" to be "Value as of the report date" (with the report date user-defined, with the choice of dates coming from the REFCALENDAR dates). I also want to group records (as there can be multiple Holdings for a single Investment in the one Fund).

As a user of Excel, I gravitate towards the use of the dlookup() function - similar in concept to VLOOKUP() and HLOOKUP() I'm sure - to which you refer (notwithstanding your reservations about memory-intensity). But frankly I'm not sure where to use it. In excel, with WYSIWYG, you know where such a function can be/is being used. With Access it's less obvious to me. I think that I can't use it in tables (upon reading help guides, Access 2007 doesn't allow calculated fields in normal tables, no?), so I have to use something else, which I assume would be a querytable. But it then looks like I am repeating info I will have in "regular" tables, which sounds like I will be contravening the rules of normalization?

dlookup("[FV_ID]", "tbl_FundValuations", "[Fund_ID] = " & [fund_id] & " AND [FV_StartDate] <= #" & [Date of Report] & "# AND [FV_EndDate] >= #" & [Date of Report] & "#")

Again, the formula above to which you refer conceptually makes sense, though the [Date of Report] I would want to be user-defined and constrained to being the list of dates in my REFCALENDAR table.

Thanks so much for your assistance on this.

Rgds - Reg

[rpeare]

Here's a simple, sample database.

on the data entry screen when a value is entered in both the fund and the date of the transaction it will retrieve the proper valuation ID, that part you can make invisible, I've just left it visible so you can see what's going on and go look at the code.

Also a simple report is attached to show the net value of purchases/sales with an AS OF date and the current value of any remaining fund shares.

RegBrown.zip

Note I didn't put in a lot of error checking etc so you can do things like put in a future date and it will probably blow up or putting in a date for which there is no valuation period etc but you can smooth that out in your database.

[Reg Brown]

Here's a simple, sample database.

on the data entry screen when a value is entered in both the fund and the date of the transaction it will retrieve the proper valuation ID, that part you can make invisible, I've just left it visible so you can see what's going on and go look at the code.

Also a simple report is attached to show the net value of purchases/sales with an AS OF date and the current value of any remaining fund shares.

RegBrown.zip

Note I didn't put in a lot of error checking etc so you can do things like put in a future date and it will probably blow up or putting in a date for which there is no valuation period etc but you can smooth that out in your database.

Thanks so much rpeare. I'll take a proper look at the database you've sent and attempt to recreate it in my database (that's the best way I find to understand the inner workings of a new application - reverse-engineer where possible). So I won't post for a while until I've dismantled, understood and recreated.

Kind regards