Last Price of the Month Query?

[nicoper]

Hello all

We have this database:




It has Symbol on the first column, Date (daily on trading days) on the second and Close on the third.

We want a query that shows the last CLOSE of the month for each symbol and sum the volume of the month.

The query table would be like this:

Symbol	Date (last date of the month)	Close (last date of the month)	Volume (sum of whole month)

Here´s the sample file:

Sample.accdb

Can you please help? I highly appreciate it.

[June7]

An aggregate (Group By) Totals query can retrieve the last date and the volume sum.

SELECT Symbol, Year([Date]) & Month([Date]) AS YrMo, Sum([Volume]) AS SumVol FROM Tablename GROUP BY Symbol, Year([Date]) & Month([Date]);

Retrieving the CLOSE that is associated with the last date is more difficult. Will likely involve nested subquery or domain aggregate function.

[nicoper]

Thank you very much June7.

Is there any way to show a readable date like:
03/2015

[June7]

Calculate year and month separately then concatenate those constructed fields in a report textbox.

SELECT Symbol, Year([Date]) AS Yr, Month([Date]) AS Mo, Sum([Volume]) AS SumVol FROM Tablename GROUP BY Symbol, Year([Date]), Month([Date]);

For retrieving the entire record that is associated with the last date for each symbol, review http://allenbrowne.com/subquery-01.html#TopN

[nicoper]

Awesome thanks. Rep given.
Will take a look at the link.

[nicoper]

Gosh, it´s looking like Chinese to me.

I´m using MLD Downloader. There is a function there to download the monthly EOD data directly from yahoo.
But the problem are the splits: It seems that you can only download the correct split adjusted data on the daily, returning incorrect data for the monthly.
So the way to fix it is downloading the daily and use an Access query that gets the last day of the month "Close".

Isn´t there an easier way to get it?

[June7]

I forgot to include expression to return last date:

SELECT Symbol, Year([Date]) AS Yr, Month([Date]) AS Mo, Sum([Volume]) AS SumVol, Max([Date]) AS LastDate FROM Tablename GROUP BY Symbol, Year([Date]), Month([Date]);


As noted, retrieving the Close that is associated with the last date for each month for each symbol is more difficult. The TOP N query demonstrated in the link is maybe the easiest method. However, it requires a unique record identifier field - an autonumber field will serve that purpose.

SELECT ID, Symbol, [Date], Close FROM EOD WHERE ID IN (SELECT TOP 1 ID FROM EOD AS Dupe WHERE Dupe.Symbol=EOD.Symbol AND Year(Dupe.[Date])=Year(EOD.[Date]) AND Month(Dupe.[Date])=Month(EOD.[Date]) ORDER BY Dupe.Symbol, Dupe.[Date] Desc);

Another method uses domain aggregate functions.

Both of those methods may perform slowly with large datasets.

Another:

Query1:
SELECT EOD.SYMBOL, Max(EOD.Date) AS MaxOfDATE, Sum(EOD.VOLUME) AS SumOfVOLUME, Year([Date]) AS Yr, Month([Date]) AS Mo
FROM EOD
GROUP BY EOD.SYMBOL, Year([Date]), Month([Date]);

Query2:
SELECT EOD.SYMBOL, EOD.DATE, EOD.CLOSE, Query1.SumOfVOLUME, Query1.Yr, Query1.Mo
FROM Query1 INNER JOIN EOD ON (Query1.MaxOfDATE = EOD.DATE) AND (Query1.SYMBOL = EOD.SYMBOL);

The two queries as nested all-in-one:
SELECT EOD.SYMBOL, EOD.DATE, EOD.CLOSE, Query1.SumOfVOLUME, Query1.Yr, Query1.Mo
FROM (SELECT EOD.SYMBOL, Max(EOD.Date) AS MaxOfDATE, Sum(EOD.VOLUME) AS SumOfVOLUME, Year([Date]) AS Yr, Month([Date]) AS Mo
FROM EOD
GROUP BY EOD.SYMBOL, Year([Date]), Month([Date])) AS Query1 INNER JOIN EOD ON (Query1.MaxOfDATE = EOD.DATE) AND (Query1.SYMBOL = EOD.SYMBOL);

This seems to run much faster.


Date is a reserved word. Should not use reserved words as names.

[nicoper]

Oh man, that´s perfect!
And fast too, I tested it with over 1 mil rows and lasted only a few secs.
I´m planning on having around 8/9 mil including the monthly from 2011 till date for all relevant symbols.
It shouldn´t be a problem since I´d only update it once a month. The rest is done in excel.

About the Date word, I know, I´ve been told that. But it´s been working ok and is used everywhere else in the data model. Replacing it means redoing everything (big pain).

Man, a huge thanks from me. Best luck!

[June7]

Using reserved words can cause issues. Happened to me. An expression in query would not recognize the field name, insisted on seeing the word with it's special meaning, even surrounding with [] did not help. I don't remember the word or the expression - just remember hitting a wall.

I use Rick Fisher's Find & Replace add-in for global edit to change a field name. Saved my sanity more than once.

[nicoper]

I never thought of that. I have a very neat tool called "Simple Search/Replace" from RJLSoftware, but it may not work with access/excel files.
Thanks for the recommendation, will take a look. I´m backing up everything, scared to death to have to rebuild everything.