Query to calculate quarterly growth rate.

[gromit1]

I have attached a file that contains a table called "Historical Prices". I need to figure out how to find the growth rate for each quarter of every year for each ticker symbol listed. So for this example I would need to find the growth rate for GOOG in the 4th quarter of 2012.

To calculate this manually I would need to take the Close Price on the last day of the 4th quarter (12/31/2012) divided by the Open Price of the first day of the 4th quarter (10/1/2012). Then I need to subtract by 1 and multiply by 100 in order to get a percentage.

The actual calculation would look like this: ((707.38/759.05)-1)*100 = -6.807

The first and last days of each quarter may vary due to weekend days. The different possibilities are listed below:

Quarter	Open Date
1	1/1	1/2	1/3
2	4/1	4/2	4/3
3	7/1	7/2	7/3
4	10/1	10/2	10/3
Quarter	Close Date
1	3/31	3/30	3/29
2	6/30	6/29	6/28
3	8/30	8/29	8/28
4	12/31	12/30	12/29

I am extremely new to Access and I am pretty confused. I am writing a Visual Basic program and I discovered that I need to use a database so now I am trying to learn! Any information that I could get that could help me on my way to learning more about Access would be greatly appreciated!

[June7]

Consider:

query MinMaxYrQtrDates
SELECT Year([Date]) & "-" & DatePart("q",[Date]) AS YrQtr, [Historical Prices].Ticker, Max([Historical Prices].Date) AS MaxDate, Min([Historical Prices].Date) AS MinDate
FROM [Historical Prices]
GROUP BY Year([Date]) & "-" & DatePart("q",[Date]), [Historical Prices].Ticker;

query2
SELECT MinMaxYrQtrDates.YrQtr, MinMaxYrQtrDates.Ticker, MinMaxYrQtrDates.MaxDate, [Historical Prices].Close, MinMaxYrQtrDates.MinDate, [Historical Prices_1].Open
FROM [Historical Prices] AS [Historical Prices_1] INNER JOIN ([Historical Prices] INNER JOIN MinMaxYrQtrDates ON ([Historical Prices].Ticker = MinMaxYrQtrDates.Ticker) AND ([Historical Prices].Date = MinMaxYrQtrDates.MaxDate)) ON ([Historical Prices_1].Date = MinMaxYrQtrDates.MinDate) AND ([Historical Prices_1].Ticker = MinMaxYrQtrDates.Ticker);

[gromit1]

This looks great, thank you! As I said I am extremely new to Access. Where would I insert this wording? Would I have to use VBA or can I insert this withing the normal Access program? Thanks again!

[June7]

Those are query statements. Use query designer to replicate. You can copy/paste the statements from post into the SQL View of designer.

[gromit1]

Great! "into the SQL View of designer" was the key for me. I've mainly only investigated Design View. Thanks I'll try this!

[gromit1]

Should I create a new table that has columns Ticker, YearQtr, and Growth Rate or should I just add a new Growth Rate column to my existing Historical Prices table and have the Growth Rate column contain duplicates for every date in each quarter?

Also, is it possible insert the SQL statements that you suggested directly into my VB.net program and have it return the Growth rates into a table? I know the general idea of how to do this I just have issues with the SQL language (which you helped with!) This is my end goal so if this could be done directly through VB.net and without having to take an extra step in Access I would like that.

[June7]

Why would a new table be needed? GrowthRate is calculated when needed. Why save to table?

I suppose the queries will work in VB.Net with connections to the Access tables.

[gromit1]

Is it correct that these have to be placed in two separate queries And then query 2 just calls upon query MinMaxYrQtrDates.

That is what I did and it seems to work for the most part. The only problem is that it does not actually calculate the growth rate. How do I do this? It returns the Max and Min dates of the quarters but does not divide the Max date by the Min date to calculate the growth rate.

Also, how would I work with partial data for quarters. Say the data starts part way through the quarter so the Min date would be say 1/10/13 and the Max date would be 3/29/13. This would not calculate an accurate growth rate. I would like to disregard quarters with partial data.

Thank you so much for all of your assistance! It is extremely helpful!

[June7]

You could do an all-in-one nested query. Where the second query has INNER JOIN MinMaxYrQtrDates, replace with:

INNER JOIN (SELECT Year([Date]) & "-" & DatePart("q",[Date]) AS YrQtr, [Historical Prices].Ticker, Max([Historical Prices].Date) AS MaxDate, Min([Historical Prices].Date) AS MinDate
FROM [Historical Prices]
GROUP BY Year([Date]) & "-" & DatePart("q",[Date]), [Historical Prices].Ticker) AS MinMaxYrQtrDates

Then you can delete the first query object.

You can do the growth calculation within the second query or in a textbox on report that uses the second query as data source.
([Historical Prices].Close / [Historical Prices_1].Open -1) * 100

[gromit1]

This is absolutely perfect! Here is what I have and it is working:

SELECT MinMaxYrQtrDates.YrQtr, MinMaxYrQtrDates.Ticker, MinMaxYrQtrDates.MaxDate, [Historical Prices].Close, MinMaxYrQtrDates.MinDate, [Historical Prices_1].Open, ([Historical Prices].[Close]/[Historical Prices_1].[Open]-1)*100 AS GrowthRate
FROM [Historical Prices] AS [Historical Prices_1] INNER JOIN ([Historical Prices] INNER JOIN [SELECT Year([Date]) & "-" & DatePart("q",[Date]) AS YrQtr, [Historical Prices].Ticker, Max([Historical Prices].Date) AS MaxDate, Min([Historical Prices].Date) AS MinDate
FROM [Historical Prices]
GROUP BY Year([Date]) & "-" & DatePart("q",[Date]), [Historical Prices].Ticker]. AS MinMaxYrQtrDates ON ([Historical Prices].Date = MinMaxYrQtrDates.MaxDate) AND ([Historical Prices].Ticker = MinMaxYrQtrDates.Ticker)) ON ([Historical Prices_1].Ticker = MinMaxYrQtrDates.Ticker) AND ([Historical Prices_1].Date = MinMaxYrQtrDates.MinDate);

I only have two remaining questions. First how would I get my growth rate figures to show as percentages with two decimal places?

Second, I would like to disregard quarters with partial data. As an example, in the file that I attached only quarter 4 of 2012 is complete so I would like to disregard all other quarters since they would not calculate an accurate quarterly growth rate. So for this example I don't want the query to show quarter 3 of 2012 or quarter 1 of 2013. Only quarter 4 of 2012.

[June7]

If you want a percentage, maybe don't multiply by 100? As stated, calcs can be done in textboxes. Use the raw data (Open and Closed) and reference those fields in expressions in textboxes to do whatever calcs you want on report.

Use textbox properties on report to set decimal display.

That filtering is tricky because condition is dependent on data in other records (or non-existence of records) of the same dataset. How do we know record for quarter 2012-03 should not be included - because some months of that quarter are missing from the original raw dataset. But there is no field that this condition can be applied to as criteria. Creating such a calculated field so it can be used as filter isn't easy. Probably will call for a custom function.

Why is 2012-03 an incomplete quarter - that was when the database was implemented and data entry started mid-quarter?

Alternatively, user could input a 'start year-quarter' and an 'end year-quarter' as a criteria range to apply as filter on the YYYY-QQ field. Use a form for this input and the input boxes can have default values.

[gromit1]

2012-03 is an incomplete quarter because the database starts on 9/28/12 which is the last day of quarter 3.

What if I included something like this:

If MinDate is greater than the 3rd day of the first month of the quarter then don't include that quarter. Or if MaxDate is less than the 28th day of the last month of the quarter. Is this possible? It's pretty important that I don't have partial quarterly growth rates returned.

[June7]

That sounds relevant.

But why the 3rd and 28th days? And the Min and Max would have to be determined for EACH quarter.

Why even bother with data input for 2012-03? Won't every quarter that follows be eventually complete so really only need to exclude the current one? That would not be difficult.

[gromit1]

You are correct but this is just an abbreviation of the database. The database will contain many different companies. Each company will have a different beginning date that will lead to many incorrect growth rates. Also the growth rate for the current quarter for every company will be abbreviated.

And I would need to use the 3rd and 28th days because I can't use the 1st day because in some months the 1st day falls on a weekend. So say the 1st is a Saturday and the 2nd is a Sunday then the actual first day of the quarter would be the 3rd. The 3rd is the latest possible day the quarter can start. So if the data was from after the 3rd day of the first month of the quarter I would want to disregard it because it would be partial and lead to a skewed quarterly growth rate. The same would be true for the last 3 days in of the last month of the quarter only in reverse.

[June7]

Okay, so might have to exclude the very first quarter of each company as well as the current quarter but every quarter in between should be complete? Oh, wait, you might stop collecting data for companies some time before the current quarter.

However, the 3rd and 28th won't apply for every quarter for every year. And what about holidays? 1/1/2013 was a Tuesday - the first day of quarter was a holiday (at least it was for me) so the first working day was 1/2/2013. What if data is not entered until 1/7/2013, should that exclude the entire quarter? And is it certain that there will be data for every working day of every month after the startup quarter?

Then there is the calculation to determine the first possible working day of quarter. Getting the first day of quarter is relatively easy. Review http://support.microsoft.com/kb/210604

Accounting for weekends and holidays is more complicated. Will need VBA custom function. http://support.microsoft.com/kb/210064