Calculation Expression

[Guest]

Hi,

I have this database where i could like to caculate the 14days average price
of a stock closing prices. How can i write the expression:

Fields that i have:

[ID] - Autonumber
[Close] - Closed price of a stock

Thank. Pls help

 

[John W. Vinson]

Hi,

I have this database where i could like to caculate the 14days average price
of a stock closing prices. How can i write the expression:

Fields that i have:

[ID] - Autonumber
[Close] - Closed price of a stock

If those are the fields you have, then You Can't Do It.

Do you also have the date for that price? Do you also have the stock ticker
or CUSIP? If so, create a Totals query; Group By the stock identifier, Average
the price, and use a criterion on the date field of

BETWEEN DateAdd("d", -14, Date()) AND Date()

to get the average price for the preceding 14 days.

John W. Vinson [MVP]

 

[Guest]

Hi

U can't use date as reference as 14days is based on the trading days which
exclude holidays and weekend.

I want to creat a field to find the 14days average on each record based on
previous.

Please help.

Nelson Chou

Hi,

I have this database where i could like to caculate the 14days average price
of a stock closing prices. How can i write the expression:

Fields that i have:

[ID] - Autonumber
[Close] - Closed price of a stock

If those are the fields you have, then You Can't Do It.

Do you also have the date for that price? Do you also have the stock ticker
or CUSIP? If so, create a Totals query; Group By the stock identifier, Average
the price, and use a criterion on the date field of

BETWEEN DateAdd("d", -14, Date()) AND Date()

to get the average price for the preceding 14 days.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi

U can't use date as reference as 14days is based on the trading days which
exclude holidays and weekend.

I want to creat a field to find the 14days average on each record based on
previous.

Please help.

I can only help you if you help me do so, Nelson. I cannot see your database,
and have no idea how your records are stored. I don't know how trading days
are defined in your database.

You would certainly use a query, but since I don't know the basis - the tables
- I cannot suggest how that query might be written!

Please post a more thorough description of your table and an example of the
date range that you would like to calculate.

John W. Vinson [MVP]

 

[Guest]

Hi John,

The following are my fields of the database:

1. [ID] - Autonumber
2. [Trading Date] - Date
3. [Stock Name] - Text
4. [Close] - Number (Stock closing price)

I have a main (Stock Historical) database where i collect all the historical
stock prices of different stock names. Then a use a query (combo box) to
select a particular stock name and append these data into a new table show as
above.

As the Trading Date is not in sequence......meaning stock market only open
on weekdays except public holiday and weekend. If a use date as reference, I
presume that the 14days will not be accurate as the Access will also count
the public holidays and weekend even it is not a trading days.

Hope the above information will be sufficient. Thank for your response.

Nelson Chou

 

[Guest]

Sorry John,

I forget to tell you what i want to calculate.

I intend a create a calculation field where i can calculate the 14days
average price of the [CLOSE] prices. Meaning :- the current record [CLOSE]
price plus 13 days before this record [CLOSE] price divide by 14days. The
field should appear on each records for that particular stock name.

Thank you.

Nelson Chou

Hi John,

The following are my fields of the database:

1. [ID] - Autonumber
2. [Trading Date] - Date
3. [Stock Name] - Text
4. [Close] - Number (Stock closing price)

I have a main (Stock Historical) database where i collect all the historical
stock prices of different stock names. Then a use a query (combo box) to
select a particular stock name and append these data into a new table show as
above.

As the Trading Date is not in sequence......meaning stock market only open
on weekdays except public holiday and weekend. If a use date as reference, I
presume that the 14days will not be accurate as the Access will also count
the public holidays and weekend even it is not a trading days.

Hope the above information will be sufficient. Thank for your response.

Nelson Chou

I can only help you if you help me do so, Nelson. I cannot see your database,
and have no idea how your records are stored. I don't know how trading days
are defined in your database.

You would certainly use a query, but since I don't know the basis - the tables
- I cannot suggest how that query might be written!

Please post a more thorough description of your table and an example of the
date range that you would like to calculate.

John W. Vinson [MVP]

 

[John W. Vinson]

Hi John,

The following are my fields of the database:

1. [ID] - Autonumber
2. [Trading Date] - Date
3. [Stock Name] - Text
4. [Close] - Number (Stock closing price)

SELECT [Stock Name], [Trading Date], [Close],
(SELECT Avg([Close]) FROM [tablename] AS X
WHERE X.ID IN(SELECT TOP 14 [ID] FROM [tablename] AS Y WHERE Y.[Stock Name] =
[tablename].[Stock Name] ORDER BY [Trading Date])) AS Avg14Day;

Replace [tablename] with your actual table name.

This will use a Subquery as a calculated field; the subquery average the most
recent 14 records (fourteen trading days, not including holidays since they
won't have records) for each stock. The only downside is if there is a stock
so thinly traded that there are days with no trades at all you'll get the
average over the last 14 days that *that particular stock* traded.

John W. Vinson [MVP]