Calculating a return where the interval is approximately 365 days

[Sanjay_S]

I have a table of Mutual Fund net asset values (NAVs) on a daily basis, called “Equity_Adj_NAV in my Access 2013 database. This table had the following fields:
Code- Each Fund has a unique code
NAV Date- The date on which the NAV is recorded
NAV- the value of the fund on that day.

A sample trunctated table in Excel 97-2003 format is attached herewith.

I want to capture 1-year returns for each fund on any date, for further use in performance analysis.

I used the following query, and it worked very well:

SELECT Equity_Adj_NAV.Code, Equity_Adj_NAV.[NAV Date], Equity_Adj_NAV.NAV AS NAV1, Equity_Adj_NAV_1.NAV AS NAV2, [NAV2]/[NAV1]-1 AS Return
FROM Equity_Adj_NAV AS Equity_Adj_NAV_1 INNER JOIN Equity_Adj_NAV ON Equity_Adj_NAV_1.Code = Equity_Adj_NAV.Code
WHERE (([Equity_Adj_NAV_1].[NAV Date]=[Equity_Adj_NAV].[NAV Date]+365));

However, there are some places where the exact “Date+365” will not work- due to holidays or non-business days, we may get a match for dates from 355 to 365 days.

In Excel, we work around this using the “TRUE” criterion in a VLOOKUP formula. Is there any way to get the nearest approximate 365-day date and compute the return, where the exact 365-day date is not available in the data?

Also, the main table has over 300 codes and 700,000 rows; the attachment is only a sub-set with 10 codes and 24,000 rows. So any solution would need to take cognizance of the table size.

 

[sneuberg]

I suggest first creating a separate query that gets the best date between the span [NAV Date]+355 and [NAV Date]+365, best being closest to [NAV Date]+365. So in your query you would use BETWEEN, sort the dates in descending order, and have TOP 1 after the SELECT. This query would also have to contain the primary key of the table with these dates. I'm guessing that's the code.

Once you have that query working you can join it with the query you have in place of the WHERE clause.

 

[smig]

what do you mean by “Date+365” will not work ?
Is it a problem when it fall on a non working date? If so, what do you want to do when this happen?

 

[Sanjay_S]

Thanks, Uncle Gizmo, for the response and the attention. Much appreciated.

Smig, you are right. What I mean when I say "Sometimes Date+365" will not work- it may be a holiday, or a weekend. In which case, I need the query to return the nearest working day, that is available in the table.

sneuberg, I tried your suggestion of using a SELECT TOP1 in the following query (Sorry, I am not wrapping this in code tags- when I tried, it was only taking part of the query and leaving the rest as text):

SELECT Equity_Adj_NAV.Code, Equity_Adj_NAV.[NAV Date], Equity_Adj_NAV.NAV AS NAV1, (SELECT TOP 1 B.[NAV]
FROM [Equity_Adj_NAV] AS B
WHERE (((B.[NAV Date] Between [Equity_Adj_NAV].[NAV Date]+355 AND [Equity_Adj_NAV].[NAV Date]+365)) AND (B.

=[Equity_Adj_NAV].Code))
ORDER BY Abs([Equity_Adj_NAV].[NAV Date] - B.[NAV Date]) DESC) AS NAV2, [NAV2]/[NAV1]-1 AS Return
FROM Equity_Adj_NAV
WHERE ((((SELECT TOP 1 B.[NAV]
FROM [Equity_Adj_NAV] AS B
WHERE (((B.[NAV Date] Between [Equity_Adj_NAV].[NAV Date]+355 AND  [Equity_Adj_NAV].[NAV Date]+365)) AND (B.[Code]=[Equity_Adj_NAV].[Code]))ORDER BY Abs([Equity_Adj_NAV].[NAV Date] - B.[NAV Date]) DESC)) Is Not Null))

This query works on the truncated 24,000 row table I had attached with my initial post. However, on the 280-code, 700,000-row table, it does display a datasheet, but that's it. I can't append this to another table, or even scroll down to see the last row, and, if I use it in another query, to, say compare vis-a-vis a benchmark and aggregate performances, the query just runs for days.

Instead of this query, if I use the first query I posted, I may get something like 25% less readings (based on the truncated table), but the query runs in a few seconds and is completely usable in other queries.

This is where I am now stuck. Please let me know if you have any thoughts.

 

[sneuberg]

If the only problem with this new query is the execution speed then I suggest trying some indexes. In the attached database I have imported the spreadsheet you attached. The Equity_Adj_NAV table in this database has a primary key which consist of the Code and Nav Date field. The Equity_Adj_NAV_NOKEY has no indexes. The Modified Query is the query you just posted and uses the Equity_Adj_NAV table. The Modified Query NOKEY uses the Equity_Adj_NAV_NOKEY table.

On my system the Modified Query NOKEY takes about 5 seconds to run. The Modified Query runs in a instant.

If you can't make the Code-Nav Date the primary key I suggest trying some non unique indexes starting with the Nav Date by itself.

Let us know it this works for you. If not there may be other ways of doing this.

 

[sneuberg]

I tested this with just a non unique index on the Nav Date. The run times are more telling if you go to the last record rather just the first screen. For this the results were:

Without Index: >26 Minutes. (I got tired of waiting and stopped Access)
With Index: 8 Seconds

 

[Sanjay_S]

sneuberg, you genius, you!

I had a concatenated field ( Code& NAV Date) as primary key for the Equity_Adj_NAV table. But when I used your method of designating both code and NAV date as primary keys, and removing the concatenated field, the query just sped up.

I used this query as an input into a more complicated aggregate query, and I had my result out in 15 minutes.

Thank you so much for the help and effort. Do you know why queries work faster when you use multiple fields as primary keys, instead of doing a concatenation of these fields and designating that as the primary key?

 

[sneuberg]

Do you know why queries work faster when you use multiple fields as primary keys, instead of doing a concatenation of these fields and designating that as the primary key?

I don't know but I would guess that it's because it was designed to work with multiple fields as primary keys.