Price at end of month!!

G

Greg

Hi all

I have a table with daily share prices for about 200 companies, spanning
about 5 years. approx 220,000 records

I am looking for an efficient bit of code to to return a query with
month end stock price for each company.

I am not sure if an array with values stored for each end of month would
be as fast as maybe a recordset in ado.

so any clues or advise would be helpful the table wil grow over time i
would like to eventually create summerised tables thus avoiding the
continuous use of the main table.

regards
Greg
 
D

Dirk Goldgar

Greg said:
Hi all

I have a table with daily share prices for about 200 companies,
spanning about 5 years. approx 220,000 records

I am looking for an efficient bit of code to to return a query with
month end stock price for each company.

I am not sure if an array with values stored for each end of month
would be as fast as maybe a recordset in ado.

so any clues or advise would be helpful the table wil grow over time i
would like to eventually create summerised tables thus avoiding the
continuous use of the main table.
Click to expand...

A query can extract the information you want, but I'd need to see the
definition of the table in order to construct one. For information on
this sort of query -- if you'd like to have a bash at it yourself -- see
this link:

http://www.mvps.org/access/queries/qry0020.htm
 
G

Greg

Hi Dirk

Thanks for the link, it gave me some clues and i manged with trial and
error to get a result see SQL below. however 2 issues

this design wont work when querying a year field IE 2000 & 2001 i would
say it is the way i have the Max function working (there can only be 1
max) right ?

Below is the table def

Table definition


Table = Daily-Stock-Prices
Fields
date Ck as date
Code Ck as string
High as double
Low as double
Close as double
Volume as long integer

SQL
SELECT [Daily-Stock-Prices].Code, Format([date],"mmm") AS [Month],
Max([Daily-Stock-Prices].Close) AS MaxOfClose
FROM [Daily-Stock-Prices]
GROUP BY Format([date],"yyyy"), [Daily-Stock-Prices].Code,
Format([date],"mm"), Format([date],"mmm")
HAVING ((([Daily-Stock-Prices].Code)="anz") AND
((Format([date],"mmm"))="jun"))
ORDER BY Format([date],"yyyy"), Format([date],"mm");

Any improvements or a faster method this queries about 230,000 rec so
far but only takes a couple of seconds i dont think that is 2 bad but im
sure there is way of speeding it up that where i need a bit of help :)

regards
Greg
 
D

Dirk Goldgar

Greg said:
Hi Dirk

Thanks for the link, it gave me some clues and i manged with trial and
error to get a result see SQL below. however 2 issues

this design wont work when querying a year field IE 2000 & 2001 i
would say it is the way i have the Max function working (there can
only be 1 max) right ?

Below is the table def

Table definition


Table = Daily-Stock-Prices
Fields
date Ck as date
Code Ck as string
High as double
Low as double
Close as double
Volume as long integer

SQL
SELECT [Daily-Stock-Prices].Code, Format([date],"mmm") AS [Month],
Max([Daily-Stock-Prices].Close) AS MaxOfClose
FROM [Daily-Stock-Prices]
GROUP BY Format([date],"yyyy"), [Daily-Stock-Prices].Code,
Format([date],"mm"), Format([date],"mmm")
HAVING ((([Daily-Stock-Prices].Code)="anz") AND
((Format([date],"mmm"))="jun"))
ORDER BY Format([date],"yyyy"), Format([date],"mm");

Any improvements or a faster method this queries about 230,000 rec so
far but only takes a couple of seconds i dont think that is 2 bad but
im sure there is way of speeding it up that where i need a bit of
help :)
Click to expand...

I want to clarify something. When you list the fields
date Ck as date
Code Ck as string
Click to expand...

are you saying that the fields named "date" and "Code" compose the
primary key of the table?

Your use of the names "date" and "Daily-Stock-Prices" cause a few
problems, because Access won't interpret them correctly unless they are
enclosed in brackets.

I'm no great SQL maven, but I think you are looking for a query along
these lines:

SELECT
P.Code,
Year(P.Date) AS CloseYear,
Month(P.Date) AS CloseMonth,
P.Close
FROM
[Daily-Stock-Prices] AS P
INNER JOIN
(SELECT
[Daily-Stock-Prices].Code,
Year([Date]) AS CloseYear,
Month([Date]) AS CloseMonth,
Max([Daily-Stock-Prices].Date) AS LastDate
FROM
[Daily-Stock-Prices]
GROUP BY
[Daily-Stock-Prices].Code,
Year([Date]),
Month([Date])
) AS D
ON (P.Date = D.LastDate) AND (P.Code = D.Code)
ORDER BY Year(P.Date), Month(P.Date);

You can apply whatever criteria you want to the result.

If this query works for you and you want to save it as a stored query,
be advised that when you save it, Access will insist on rewriting it in
its own idiosyncratic syntax with brackets instead of parentheses around
the subquery, which will make it hard to modify later because Access
will get confused between the subquery's brackets and the brackets
around the field and table names. If you were not using those
unfortunate names the problem wouldn't arise.
 
G

Greg

Hi Dirk
to answer your question, yes they are a composite Key, should have been
a bit clearer,

Thanks for your explanation of how access interprets things( explains a
few problems i have had from time to time learning SQL) and will spend
more time considering names of fields.

I had actually started this as a spurt of inspiration with out much
thought and have found the project very good for learning some of the
more complex tasks that can be achieved.

Im sure there are better programs out there for this sort of stuff,
but i like a challenge and it helps your learning.

Will give the code a try and post back with some results, will take a
bit of time as i am in the middle of exams at present(no not database
programming) thats next semester :) I do my own assignments

all the best
Greg

Dirk said:
Hi Dirk

Thanks for the link, it gave me some clues and i manged with trial and
error to get a result see SQL below. however 2 issues

this design wont work when querying a year field IE 2000 & 2001 i
would say it is the way i have the Max function working (there can
only be 1 max) right ?

Below is the table def

Table definition


Table = Daily-Stock-Prices
Fields
date Ck as date
Code Ck as string
High as double
Low as double
Close as double
Volume as long integer

SQL
SELECT [Daily-Stock-Prices].Code, Format([date],"mmm") AS [Month],
Max([Daily-Stock-Prices].Close) AS MaxOfClose
FROM [Daily-Stock-Prices]
GROUP BY Format([date],"yyyy"), [Daily-Stock-Prices].Code,
Format([date],"mm"), Format([date],"mmm")
HAVING ((([Daily-Stock-Prices].Code)="anz") AND
((Format([date],"mmm"))="jun"))
ORDER BY Format([date],"yyyy"), Format([date],"mm");

Any improvements or a faster method this queries about 230,000 rec so
far but only takes a couple of seconds i dont think that is 2 bad but
im sure there is way of speeding it up that where i need a bit of
help :)
Click to expand...


I want to clarify something. When you list the fields

date Ck as date
Code Ck as string
Click to expand...


are you saying that the fields named "date" and "Code" compose the
primary key of the table?

Your use of the names "date" and "Daily-Stock-Prices" cause a few
problems, because Access won't interpret them correctly unless they are
enclosed in brackets.

I'm no great SQL maven, but I think you are looking for a query along
these lines:

SELECT
P.Code,
Year(P.Date) AS CloseYear,
Month(P.Date) AS CloseMonth,
P.Close
FROM
[Daily-Stock-Prices] AS P
INNER JOIN
(SELECT
[Daily-Stock-Prices].Code,
Year([Date]) AS CloseYear,
Month([Date]) AS CloseMonth,
Max([Daily-Stock-Prices].Date) AS LastDate
FROM
[Daily-Stock-Prices]
GROUP BY
[Daily-Stock-Prices].Code,
Year([Date]),
Month([Date])
) AS D
ON (P.Date = D.LastDate) AND (P.Code = D.Code)
ORDER BY Year(P.Date), Month(P.Date);

You can apply whatever criteria you want to the result.

If this query works for you and you want to save it as a stored query,
be advised that when you save it, Access will insist on rewriting it in
its own idiosyncratic syntax with brackets instead of parentheses around
the subquery, which will make it hard to modify later because Access
will get confused between the subquery's brackets and the brackets
around the field and table names. If you were not using those
unfortunate names the problem wouldn't arise.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Apply Month End date based on transaction date 5
How to structure a Table with many, many, many Fields? 16
Trouble with Max function 5
Investment database, how to update prices? 0
Access mdb or SQL Server Back End? 4
giving a presentation on why accountants should learn someaccess...is what i say fairly accurate? 1
Search Listbox vs Table Query 1
Create Hyperlinks 2

Top