Access Query - How Do I Write a Query to Use Data Associated with Dates in the Past?

[Hamish4444]

I want to create a query that calculates daily investment returns. The formula would be Market Value today divided by Market Value yesterday. Where I'm struggling is how to tell access to grab the market value from yesterday. My current TotalMarketValue query looks like this:

[TABLE="width: 500"]
<tbody>[TR]
[TD]TotalMV[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]11/01/2016[/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]11/02/2016[/TD]
[/TR]
[TR]
[TD]132[/TD]
[TD]11/03/2016[/TD]
[/TR]
</tbody>[/TABLE]

I want to create a new query that divides the 132 by 110 to produce 20% daily return for 11/03/2016; and then 110 / 100 = 10% for 11/02/2016.

The output would look as follows.

[TABLE="width: 500"]
<tbody>[TR]
[TD]DailyReturn[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]10%[/TD]
[TD]11/02/2016[/TD]
[/TR]
[TR]
[TD]20%[/TD]
[TD]11/03/2016[/TD]
[/TR]
</tbody>[/TABLE]

I'm just not sure how to tell Access to use the market value from a day in the past as the denominator.

Any help would be greatly appreciated!

Thanks.

 

[Joe4]

Well, based on your example, you can use DLOOKUP to look up the MV from the previous day like this:

PrevMV: DLookUp("TotalMV","TableName","Date=#" & [Date]-1 & "#")

However, where you may run into trouble is if there are gaps in your dates (i.e. if you don't have records for weekends or holidays). Then looking back one day will not find anything.

 

[Hamish4444]

Thanks for the help!

Great point. Maybe I will add an ID field, so it looks up the date and the ID and then grabs the market value on the date of "ID - 1" and associated market value.

 

[Joe4]

Maybe I will add an ID field, so it looks up the date and the ID and then grabs the market value on the date of "ID - 1" and associated market value.

You would just need to update your process to make sure that they are added in sequential order (do NOT use Autonumber to do this - it can have gaps).

I was searching the internet, and found using DLOOKUP with MAX, but that won't quite work. We would want MAX date that is less than the date on the record.
There has to be a way to do it with formulas, I am just not sure how. I could come up with a User Defined Function to do it.

 

[xenou]

Querywise, here's another example:

select (t.TotalMV - tMinus1.TotalMV)/tMinus1.TotalMV As PCTChange
from
	MyTable t
	inner join MyTable tMinus1
	on tMinus1.[Date] = (t.[Date] - 1)

good point about dates. You'd have to think hard about that. Might need a table of dates to determine the real previous day, or simply use the most recent last date (which would probably work for market data since there new data every day the market is open).

 

[Hamish4444]

Ok great, we're getting there.

Let me elaborate a bit more. You are right - because it's market data, I have an autoexec macro that automatically adds the closing market value and associated date every day.

I have a new idea. Let's forget for now the calculation of daily performance. How do I simply add a field that is the the Market Value of the most recent day minus 1?

So I want to create a new query with the following output:

[TABLE="width: 500"]
<tbody>[TR]
[TD]TotalMV[/TD]
[TD]Date[/TD]
[TD]PrevMV[/TD]
[/TR]
[TR]
[TD]100[/TD]
[TD]11/01/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]110[/TD]
[TD]11/02/2016[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]132[/TD]
[TD]11/03/2016[/TD]
[TD]110[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

How do I simply add a field that is the the Market Value of the most recent day minus 1?

That is exactly what the formula I posted up in post #2 does.
Just insert the correct table name.

 

[Hamish4444]

Thanks Joe. I used the formula; however, it only seemed to input the PrevMV for one of the dates.

Total MV        Date                PrevMV
100               11/01/2016      
110               11/02/2016       100
132               11/03/2016

Any insight into what happened?

This is what I did:

PrevMV: DLookUp("TotalMV","TotalMarketValue","SettlementDate=#" & [SettlementDate]-1 & "#")

 

[Joe4]

No, when I tested it out on your data, it returned numbers for the last two dates (it obviously will not return anything for the first one, since there are no dates before it).
One thing to check is your dates. If there is any time component other than 12:00 AM, it will not match up (regardless of whether or not the time component is hidden or not).
You may want to temporarily change the format of your date to one that also shows the time, so you can see what is actually stored in there, and if you have anything other than 12:00 AM.