[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 923
  • Last Modified:

Trying to return stock price data and moving average for a list of stocks.

I'm trying to return the opening price(PX_OPEN), closing price (PX_LAST), and a 3-day moving average of the closing price (just for example) from a table of stock price information structured as:

ticker    char,
pdate    date,
PX_LAST   short

To be clear, let's say that I want to return this info (open, close, 3-day average) for the most recent 30 days.  I've tried the following to get this info for ALL days (just for starters):

SELECT ticker, pdate, PX_OPEN, PX_LAST, AVG(PX_LAST)
FROM pricedata
WHERE pdate Between DATEADD(d,-3,pdate) And pdate
GROUP BY ticker;

and I'm getting an error "You tried to execute a query that does not include the specified expression 'pdate' as part of an aggregate function."  Not sure what's going wrong here.
0
mplevine
Asked:
mplevine
  • 11
  • 6
1 Solution
 
boblahCommented:
Hi mplevine,

to get the three day average for today:

SELECT ticker, AVG(PX_LAST)
FROM stocks
WHERE mdate Between DATEADD(d,-3,GETDATE()) And GETDATE()
GROUP BY ticker

now, if you want the three day average for all the dates on the db, then:

SELECT ticker, mdate, PX_Last, (SELECT AVG(PX_Last) FROM stocks AS s2 WHERE s2.ticker = s1.ticker AND s2.mdate BETWEEN DATEDIFF(d, -3, s1.mdate) AND s1.mdate) AS PX_LastThreeDayAverage
FROM stocks AS s1


Cheers!
0
 
boblahCommented:
mplevine,

I've used the mdate from the previous example, but should be obvious how to modify.
0
 
boblahCommented:
mplevine,

and for transactions that appear on the database for the last 30 days:

SELECT ticker, mdate, PX_Last, (SELECT AVG(PX_Last) FROM stocks AS s2 WHERE s2.ticker = s1.ticker AND s2.mdate BETWEEN DATEDIFF(d, -3, s1.mdate) AND s1.mdate) AS PX_LastThreeDayAverage
FROM stocks AS s1
WHERE s1.mdate BETWEEN DATEADD(d, -30, GETDATE()) AND GETDATE()
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
mplevineAuthor Commented:
I think there's a problem with the DATEDIFF function because when I run this, Access gives me a popup saying "Enter parameter value" for "d".  
0
 
mplevineAuthor Commented:
Sorry -- also the dates might not be contiguous.  For example, weekends are excluded, so I'd rather average the most recent 3 days worth of price data for each date.
0
 
boblahCommented:
mplevine,

I think there is a fence post error - for the last thirty days including today, you want:

SELECT ticker, mdate, PX_Last, (SELECT AVG(PX_Last) FROM stocks AS s2 WHERE s2.ticker = s1.ticker AND s2.mdate BETWEEN DATEDIFF(d, -3, s1.mdate) AND s1.mdate) AS PX_LastThreeDayAverage
FROM stocks AS s1
WHERE s1.mdate BETWEEN DATEADD(d, -29, GETDATE()) AND GETDATE()

but you probably actually want 30 days running back from yesterday, as you might not yet have data for today, so

SELECT ticker, mdate, PX_Last, (SELECT AVG(PX_Last) FROM stocks AS s2 WHERE s2.ticker = s1.ticker AND s2.mdate BETWEEN DATEDIFF(d, -3, s1.mdate) AND s1.mdate) AS PX_LastThreeDayAverage
FROM stocks AS s1
WHERE s1.mdate BETWEEN DATEADD(d, -30, GETDATE()) AND DATEADD(d, -1, GETDATE())

0
 
mplevineAuthor Commented:
Your answer works when I put quotes around the 'd' in DATEADD:

SELECT s1.ticker, s1.pdate, s1.PX_LAST, (SELECT AVG(PX_Last) FROM pricedata AS s2 WHERE s2.ticker = s1.ticker AND s2.pdate BETWEEN DATEADD('d', -2, s1.pdate) AND s1.pdate) AS PX_LastThreeDayAverage
FROM pricedata AS s1;

but again I would prefer to use the most recent 3 days of data which might not be contiguous due to weekends and holidays.  I'd like to do something like:

SELECT s1.ticker, s1.pdate, s1.PX_LAST, (SELECT AVG(TOP 3 PX_Last) FROM pricedata AS s2 WHERE s2.ticker = s1.ticker) AS PX_LastThreeDayAverage FROM pricedata AS s1;

But SQL doesn't accept this and I'd have to sort by dates in descending order for each ticker.  Any thoughts?
0
 
boblahCommented:
Logically what you are looking for is

SELECT s1.ticker, s1.pdate, s1.PX_LAST, AVG(s3.PX_LAST) AS PX_LastThreeDayAverage
FROM
    pricedata AS s1
    CROSS JOIN (SELECT TOP 3 s2.PX_LAST
        FROM pricedata s2
      WHERE s2.ticker = s1.ticker AND s2.pdate <= s1.pdate
      ORDER BY s2.pdate DESC) AS s3
 

give that a go, there may be some problems, post back if so.
0
 
LowfatspreadCommented:
try this

select s1.ticker,s1.pdate,s1.px_last
       ,Avg(s2.px_last)
  From pricedate as s1
 Inner Join Pricedate as s2
    on s1.ticker=s2.ticker
   and s1.pdate>=s2.pdate
 group by s1.ticker,s1.pdate,s1.px_last
 having count(*) =3


you should note however that this is the MS SQL Server Topic Area
not the MS Access Topic Area

 
0
 
boblahCommented:
Lowfatspread

How does that specify which three records from s2 are used for the average? It appears to be non-deterministic.
0
 
mplevineAuthor Commented:
Boblah, I've tried your last statement but am getting a "Syntax error in the FROM clause" and "CROSS" in the cross join gets highlighted.  I added a semicolon to the end of the statement.

SELECT s1.ticker, s1.pdate, s1.PX_LAST, AVG(s3.PX_LAST) AS PX_LastThreeDayAverage
FROM
    pricedata AS s1
    CROSS JOIN (SELECT TOP 3 s2.PX_LAST
        FROM pricedata s2
     WHERE s2.ticker = s1.ticker AND s2.pdate <= s1.pdate
     ORDER BY s2.pdate DESC) AS s3;
 

Lowfatspread, thanks for your help but your answer returns only one date per ticker while I need them all, or at least a bunch of dates within a range for each ticker.

0
 
boblahCommented:
ok, i wasn't sure about that syntax. Try the following

SELECT
    s1.ticker,
    s1.pdate,
    s1.PX_LAST,
    AVG(s3.PX_LAST) AS PX_LastThreeDayAverage
FROM
    pricedata s1
    LEFT JOIN pricedata s2
        ON s1.ticker = s2.ticker
            AND s1.pdate >= s2.pdate
WHERE
    s2.pdate IN (SELECT TOP 3 s3.pdate
                 FROM   pricedata s3
                 WHERE  
                     s1.ticker = s3.ticker
                     AND s1.pdate >= s3.pdate
                 ORDER BY s3.pdate DESC)
GROUP BY
    s1.ticker,
    s1.pdate,
    s1.PX_LAST
0
 
boblahCommented:
sorry, make that

SELECT
    s1.ticker,
    s1.pdate,
    s1.PX_LAST,
    AVG(s2.PX_LAST) AS PX_LastThreeDayAverage
FROM
    pricedata s1
    LEFT JOIN pricedata s2
        ON s1.ticker = s2.ticker
            AND s1.pdate >= s2.pdate
WHERE
    s2.pdate IN (SELECT TOP 3 s3.pdate
                 FROM   pricedata s3
                 WHERE  
                     s1.ticker = s3.ticker
                     AND s1.pdate >= s3.pdate
                 ORDER BY s3.pdate DESC)
GROUP BY
    s1.ticker,
    s1.pdate,
    s1.PX_LAST
0
 
mplevineAuthor Commented:
Boblah--

Yes this last query works perfectly.  It's extrememly slow (running on 335 tickers with perhaps 150 prices/dates each took my 3 ghz computer about 15 minutes to complete), but it does work.  I'd really be greatful (if you wouldn't mind) if you could suggest any ways of speeding it up.  Many, many thanks.

One other question-- you're obviously EXTREMELY good with SQL.  Where did you learn it and can you recommend any good books?  Many thanks.
0
 
boblahCommented:
I was concerned about the performance, that's why I tried the subquery in the from clause.

In the final query, you are having to do a whole table non-equi join twice over (actually, more than twice), which is a bit daft.

I think a procedural cursor approach might be much better performance than a single SQL statement.

psuedo code for the sort of thing you'd want would be:

for each row in pricedata
    insert into #temptable
    SELECT TOP 3 row.ticker, row.pdate, row.PX_LAST, s3.PX_LAST as ForAvg
                 FROM   pricedata s3
                 WHERE  
                     row.ticker = s3.ticker
                     AND row.pdate >= s3.pdate
                 ORDER BY s3.pdate DESC
end for

select ticker, pdate, PX_LAST, AVG(ForAvg) AS threedayavg
from #temptable
group by ticker, pdate, PX_LAST

Obviously, the T-SQL code would actually look completely different to that, but that's the structure of what you'd be doing.

0
 
mplevineAuthor Commented:
OK, I'll work on the pseudocode from here.  Many many thanks!
0
 
boblahCommented:
actually, one tweak that might help (tho it still won't be fast) (and make sure it produces the correct results) is

SELECT
    s1.ticker,
    s1.pdate,
    s1.PX_LAST,
    AVG(s2.PX_LAST) AS PX_LastThreeDayAverage
FROM
    pricedata s1
    LEFT JOIN pricedata s2
        ON s1.ticker = s2.ticker
WHERE
    s2.pdate IN (SELECT TOP 3 s3.pdate
                 FROM   pricedata s3
                 WHERE  
                     s1.ticker = s3.ticker
                     AND s1.pdate >= s3.pdate
                 ORDER BY s3.pdate DESC)
GROUP BY
    s1.ticker,
    s1.pdate,
    s1.PX_LAST
0
 
boblahCommented:
as for the books question: you flatter me. I'm entirely self taught, any impression of competence merely derives from having been doing it a long time. The only book on SQL I have ever read is "Joe Celko's SQL for Smarties: Advanced SQL Programming", which is quite good
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 11
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now