How to add an "All Time Max Price" calculated field

[nicoper]

Hello

Say we have a table like the following, with hundreds of symbols:

SYMBOL	DATE	OPEN	HIGH	LOW	CLOSE	VOLUME	ALLTIMEMAX
BIDU	03-01-2012	$120.00	$125.14	$120.00	$124.29	7311200
BIDU	04-01-2012	$122.75	$123.60	$121.15	$121.99	4398200
BIDU	05-01-2012	$121.10	$124.00	$120.68	$123.27	4772700
BIDU	06-01-2012	$123.72	$124.11	$120.00	$121.09	4978100
BIDU	09-01-2012	$122.97	$122.97	$117.17	$120.11	5594500
BIDU	10-01-2012	$124.21	$127.30	$124.00	$126.88	6929000
BIDU	11-01-2012	$126.47	$128.59	$125.53	$128.19	5328900

and we need to add the ALLTIMEMAX column. This is the MAX CLOSE price for the symbol on SYMBOL till the date on DATE.

How can we solve this efficiently?

I think that if we can take the first CLOSE for each symbol, then for the following date we´d just need to check if the new CLOSE is bigger than the previous. If it is, use it and if not, just keep the old one.



Could that be possible? How would it be?

I´d appreciate any help that you can provide.

[June7]

A DMax() domain aggregate function calc is one way.

SELECT *, DMax("Close", "tablename", "Symbol='" & [Symbol] & "' AND [Date]<=#" & [Date] & "#") AS AllTimeMax FROM tablename;

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

[nicoper]

Hi June7

Thank you so much for your reply.
I´m very new to Access and been trying to make it work with no luck yet.
Cannot figure out what I´m doing wrong.

Here´s a sample file:
All Time Max Price.zip

I hate to ask too much, but can you please help?

[June7]

What you want requires a query. What I suggested is a query. The db does not include any queries. The AllTimeMax value is a calculated result and will not be in the table. Use the query builder. Switch to SQL View to see the SQL statement. The final result should be like the suggestion but with the correct table name.

[nicoper]

Ok, great. Thank you for the clarification.
I´ve done it now and the results are here:

All Time Max Price2.zip

As you can see, something is wrong: the max price is going up and down when it should go only up.
Plus there are some missing days for some reason.

Any ideas?

[June7]

As far as I can see, it's working properly.

The query returns the max Close for each symbol up to the date of each record.

BIDU starts at 124.29 and progressively increases to 226.5

FB starts at 38.23 and progressively increases to 75.29

GOOGL starts at 333.04 and progressively increases to 610.7

Dates are missing because there are no records for them. There are no records dated 1/7/2012, 1/8/2013 for BIDU, etc.

[nicoper]

Please take a look at this screenshot:



Are you saying that it´s ok on your end? The data is all wrong on mine (like the picture above).
What could it be?

Probably an ordering issue, but not really sure.

[June7]

Sorry, the screenshot is not opening and I tried 2 browsers.

I did nothing but open your query and it looks good to me.

[nicoper]

You can try on the imgur site
http://imgur.com/yTWtKXE

or in the zip here:errors.zip

It was probably the PNG format.

[nicoper]

For some reason I cannot upload images from my computer (the buttons dissapear on the form) I´ve tried it in Firefox and Chrome with the same results, it should be a problem with the site´s form.

I drop an Jpeg just to be sure:
http://i.imgur.com/n82Oi67.jpg?1

[June7]

Agh! You have the dates in non-U.S., non-Access structure. That could definitely be an issue. Review http://allenbrowne.com/ser-36.html

I got the zipped image, blocked from the imgur site. Here is copy/paste from the query.

SYMBOL	DATE	OPEN	HIGH	LOW	CLOSE	VOLUME	AllTimeMax
BIDU	2/6/2012	$130.81	$132.78	$129.72	$131.37	6010200	134.53
BIDU	2/7/2012	$131.02	$132.23	$128.94	$129.49	5828200	134.53
BIDU	2/8/2012	$130.76	$132.26	$129.29	$130.89	5331500	134.53
BIDU	2/9/2012	$131.65	$135.83	$131.00	$135.45	6766800	135.45
BIDU	2/10/2012	$134.28	$138.00	$133.49	$136.59	7129400	136.59
BIDU	2/13/2012	$138.80	$140.09	$137.13	$140.00	6231900	140
BIDU	2/14/2012	$141.72	$142.22	$139.68	$140.86	7138800	140.86
BIDU	2/15/2012	$142.09	$142.36	$137.38	$138.32	6128400	140.86
BIDU	2/16/2012	$139.61	$142.49	$137.55	$141.83	10380400	141.83
BIDU	2/17/2012	$140.92	$140.92	$134.68	$136.90	15891200	141.83
BIDU	2/21/2012	$136.93	$137.95	$129.02	$130.64	10789500	141.83

[nicoper]

Yep, that fixed it. Thanks a ton, man. I highly appreciate it.

[nicoper]

So I went ahead and tried it in the big database with 100s of symbols (approx 1600 symbols, 500mb file) and it completely saturate Access.

Then I separated the columns in the query and added a criteria to the Date to show only the last year and got the same results.
Also with 3 months it still gets saturated.

I guess the database is too big for this method.

Is there any other way around this? I mean, to get that exact info from such a big file?

[June7]

True, domain aggregates can be slow in queries.

Maybe some adjustment to Allen Browne's Year to Date nested query example. Instead of Sum, use Max. http://allenbrowne.com/subquery-01.html#YTD

[nicoper]

I don´t know SQL and cannot see how it would be.

If is not too much, would you please help?