Extracting previous values 1

[sssu]

Hi,

I need some help.
I have two tables-
Table 1 has earnings dates
Table 2 has Daily market cap. I want the market cap of a firm on each earnings date( i am aware this can be done by simply matching the companies and dates of the two tables). My problem is not all my earnings dates are in the daily data table- can I tell it to take the previous entries market cap data provided it is for the same company.

Assistance is appreciated.
Thanks

 

[JonFer]

Add both tables in your query but only join on the company field. For the date in the market cap table, put this criteria (include the parentheses around it):

(Select Max(a.DailyCapDate) 
from TableMarketCap as a
Where a.Company=TableEarningsDates.Company and a.DailyCapDate<=TableEarningsDates.EarningsDate)

John

http://www.interautomateconsulting.com

 

[sssu]

Hi JonFer,
I'm having some difficulty.

My tables are called
EarningsDates
MktCapValues

I have written
(SELECT Max(MktCapValues.Field2) AS Expr1
FROM MEarningsDates, MktCapValues WHERE (((MktCapValues.Field2)=(EarningsDates.Ticker)) AND ((MktCapValues.MyDate)<=(EarningsDates.Datestring))));

I receive the error message- syntax error in union query.

Help?
Thank you.

 

[JonFer]

Write the query first with the two tables joined on both company and the date. Get all of the fields you want and include the date from the daily market cap table.

Then remove the join line on the date and put the SQL I posted (adjusted for your tables and fields) as the CRITERIA for the daily market cap date.

John

http://www.interautomateconsulting.com

 

[sssu]

Hi John,

Sorry about this.
The query runs now- however, I'm getting every value for each stock. So if I want only the market cap for company A at 29/06/2002 I will get every market cap for company A in the market cap table.
Can you help?

My sql is
SELECT DISTINCT EarningsDates.*, MktCapValues.MyDate, MktCapValues.Field6
FROM EarningsDates INNER JOIN MktCapValues ON EarningsDates.Ticker = MktCapValues.Field2,
(Select Max( MktCapValues.MyDate) from MktCapValues, EarningsDates Where MktCapValues.Field2=EarningsDates.Ticker and MktCapValues.MyDate<= EarningsDates.Datestring);

 

[JonFer]

You put the date criteria in the ON list. That won't work.

Assuming MyDate and Datestring are both date values (or at least are in the same format and sort in date order):

SELECT EarningsDates.*, MktCapValues.MyDate, MktCapValues.Field6
FROM EarningsDates INNER JOIN MktCapValues ON EarningsDates.Ticker = MktCapValues.Field2
Where MktCapValues.MyDate=(Select Max(a.MyDate) from MktCapValues as a Where a.Field2 = EarningsDates.Ticker and MktCapValues.MyDate <= EarningsDates.Datestring);

John

http://www.interautomateconsulting.com

 

[sssu]

Me again...

Could you offer any advice as to why it doesn't work. I press run but it doesn't run. My SQL is

SELECT EarningsDates.*, MktCapValues.MyDate, MktCapValues.Field6
FROM EarningsDates INNER JOIN MktCapValues ON EarningsDates.Ticker = MktCapValues.Field2
WHERE ((( MktCapValues.MyDate)=(Select max(a.MyDate) from MktCapValues as a Where a.Field2= EarningsDates.Ticker and MktCapValues.MyDate<= EarningsDates.Datestring)));

Thanks in advance

 

[JonFer]

Do "MyDate" and "DateString" have the DateTime field type in their respective tables? Can you post some sample rows from each table?

What do you mean by it doesn't run? Does it return no records? Do you get an error?

John

http://www.interautomateconsulting.com

 

[sssu]

Hi John,

I don't get an error message- but it just doesn't change. I click run and then it freezes- the little blue squares down the bottom left hand side of the screen that form when you run a query don't appear.

Yes MyDate and Datestring are both Date/Time formats.

In this sample one of the Earnings Dates for AAA is 28/02/1997 but this doesn't exist- so I want it to extract 27/02/1997.

This is the earnings table
Ticker Date Datenum Datestring SUE
AAA 1996 729084 29/02/1996 -1.14583412905148E-AAA 1997 729449 28/02/1997 -1.14777158811549E-AAA 1998 729813 27/02/1998 1.13754139386739E-07

This is the MarketCap Table
Field2 MyNumber MyDate Field6
AAA 19970226 26/02/1997 162605996
AAA 19970227 27/02/1997 162334353
AAA 19970228 29/02/1997 162943355
AAA 19970303 3/03/1997 163428184

Does this help? Could it be that it takes a remarkably long time. I have 1900 earnings records and approx 550000 market cap dates.

Thanks again.

 

[JonFer]

It's probably slow because of the large number of records being searched through each time. Make sure the company fields (Ticker and Field2) are indexed for both tables. Let the query run a few minutes. If it is still slow...

1. Add a compound index on the company and date fields in the MarketCap table. Do this using the Index window in the table design view.

2. Write a query that gets the market cap date for each earnings date. Add any other fields you want from EarningsDates here as well.

SELECT EarningsDates.Ticker, EarningsDates.DateString, Max(MktCapValues.MyDate) as MyDate 
FROM EarningsDates INNER JOIN  MktCapValues ON EarningsDates.Ticker =  MktCapValues.Field2
WHERE MktCapValues.MyDate)<=EarningsDates.Datestring));

Run this query to see how fast it is.

3. Combine this query with the MarketCap table to get your results. Join on the company and date fields. This should be much faster.



John

http://www.interautomateconsulting.com

 

[JonFer]

The SQL above was bad - here is a correction:

SELECT EarningsDates.Ticker, EarningsDates.DateString, Max(MktCapValues.MyDate) as MyDate 
FROM EarningsDates INNER JOIN  MktCapValues ON EarningsDates.Ticker =  MktCapValues.Field2
WHERE MktCapValues.MyDate<=EarningsDates.Datestring
GROUP BY EarningsDates.Ticker, EarningsDates.DateString

John

http://www.interautomateconsulting.com

 

[sssu]

You're a genius!
Thank you