Calculating ATR

[DianaW]

Dear All,

I need some help with my SQL query... I am trying to build a SQL query with which I can calculate the so-called Average True Range (ATR) (-> http://stockcharts.com/help/doku.php?id=chart_school:technical_indicators:average_true_range_a).

I have a table which contains stock prices; the columns are Ticker, dDate, Open, High, Low, Close, Volume.


What I have done so far is a query which gives me four columns:

• Ticker
• The current High less the current Low.
• The absolute value of the current High less the previous Close.
• The absolute value of the current Low less the previous Close.

dRefDate = #11/28/2008#
 
    query1 = "SELECT Ticker, Abs(High-Low) AS HiLo, High, Low" & _
                " FROM Data.txt" & _
                " WHERE dDate=" & CDateSQL(dRefDate) & _
                " GROUP BY Ticker, High-Low, High, Low"
 
    query2 = "SELECT TOP 1 Ticker, Close AS CloseYday" & _
                " FROM Data.txt" & _
                " WHERE dDate<=" & CDateSQL(dRefDate) & _
                " GROUP BY dDate, Ticker, Close" & _
                " ORDER BY dDate DESC"
    query3 = "SELECT Q1.Ticker, Q1.HiLo, Abs(Q1.High-Q2.CloseYday) AS ClHi, Abs(Q1.Low-Q2.CloseYday) AS ClLo" & _
                " FROM (" & query1 & ") AS Q1 INNER JOIN (" & query2 & ") AS Q2" & _
                " ON Q1.Ticker = Q2.Ticker"

How do I have to adjust the code so that I do not only get the result for one date (i.e. dDate= dRefDate) but for let's say the last 60 trading days? Any ideas?

Diana

 

[namliam]

LOL

http://www.access-programmers.co.uk/forums/showthread.php?t=161518

I know that isnt help, but ... funny to say the least !


Have you tried simply deducting 60 days from your date??

 

[DianaW]

Well spotted. We are indeed working together on this problem.

Regarding the question:
I / we want to have the result for each of the last 60 trading days. And I guess there is an alternative way than just repeating the query just 60 times...

Diana

 

[namliam]

I have no clue what exactly you are doing or why....

i.e. query2 makes no sence at all...

    query2 = "SELECT TOP 1 Ticker, Close AS CloseYday" & _
                " FROM Data.txt" & _
                " WHERE dDate<=" & CDateSQL(dRefDate) & _
                " GROUP BY dDate, Ticker, Close" & _
                " ORDER BY dDate DESC"

Why do both TOP 1 and GROUP BY??
And why not simply get it in Query1?? If you take out the TOP 1 query2 = Query1

 

[namliam]

Have you tried simply deducting 60 days from your date??

And... to answer your question (again)...

 

[DianaW]

What I am trying to do:

(1) For date t=0 I need the difference between High and Low. That's done by Query one.

Then I need (2) the (absolute) difference between the Close of t-1 and the High of t=0 and (3) the Low of t=0, respectively. The High and Low are coming from Query 1 and the Close of t-1 should come from Query 2. But I agree, there is a mistake, it should be dDate< dRefDate:

    query2 = "SELECT TOP 1 Ticker, Close AS CloseYday" & _
                " FROM Data.txt" & _
                " WHERE dDate<" & CDateSQL(dRefDate) & _
                " GROUP BY dDate, Ticker, Close" & _
                " ORDER BY dDate DESC"

These three calculations I need for each of the last 60 trading days. That is, the table should look like this:

Col1 - Col2 - Col3 - Col4 - Col5
Ticker - Date1 - (High-Low) - (High-Prev. Close) - (Low-Prev. Close)
...
Ticker - Date60 - (High-Low) - (High-Prev. Close) - (Low-Prev. Close)

I used the Top1 because dRefDate could be a Monday and the last trading day would be a Friday (i.e. -3 days rather than just -1 day)...
If I just deduct 60 I just have 60 calendar days. However, I need the data for the last 60 trading days (the table only contains trading days).

Just to explain what I would do with the table:
I would copy then the result into an array, determine the maximum column for each date and take a simple average of this maximums. The result would then be the Average True Range (ATR) for t=0.

Still I'm a beginner - so if you have an easier way please let me know...

Diana

 

[Brianwarnock]

Can I ask why we are wrestling with Access when the link uses Excel?

Brian

 

[DianaW]

Hi Brian,

I'm not sure what you mean... The final result will be used in Excel and the data source is a txt-file. However, I want to use SQL to retrieve the data (thus the question in this forum).

Usually I try out my SQL statements in Access but eventually use Excel as I know it better...

Diana

 

[Brianwarnock]

But you seem to be doing some of the calculations in Access, I would have thought that you just needed Open high low close per day, per ticker? then do all the high-low etc in EXcel as per the link.

Brian

 

[namliam]

If I understand correctly your query2 takes the close from yesterday, right??

I would do that using a function to store/retrieve that information. Or simply use a query... queries are immensly powerfull if you know how to use them.

This will dynamicaly create a junction query between "today" and "yesterday".
I.e. today, yesterday would be friday... or basicaly any date that is prior to today with a record in your table

SELECT [T].[YourDate] as Today, Max(Y.YourDate) AS Yesterday
FROM YourTable as T, YourTable AS Y
WHERE Y.YourDate<[T].[YourDate]
GROUP BY [T].[YourDate];

This result you can use to get the results from yesterday and today on the same line.
Once on the same line/record, you can do just about anything you want.

Good Luck!

 

[DianaW]

Hi Namliam,

yes, you are right and thank you for your help - it really helps me to learn everything step by step. I've "translated" now your suggestion, however Excel (with which I run the query) just hangs up with no error message... I guess that there is a problem when I run such a query with a Jet database... Or am I making another mistake???

(I absolutely realize that this is an Access forum, however I started this thread here as initially it was a pure query-related question.)

Sub RunAnalysis()
    Dim vRst() As Variant
    Dim sSQL As String
    Dim i As Integer, j As Integer
    Dim sTicker As String
 
    sTicker = "5401"
'this works:
'    sSQL = "SELECT * FROM 5401.txt"
 
'this doesn't:
    sSQL = "SELECT Q1.dDate AS dToday, Max(Q2.dDate) AS dYesterday" & _
                " FROM 5401.txt AS Q1, 5401.txt AS Q2" & _
                " WHERE Q2.dDate<Q1.dDate" & _
                " GROUP BY Q1.dDate"
 
    vRst() = TextTimeSeriesDataImport(sTicker, sSQL)
 
    With Worksheets("Tabelle1")
        .Unprotect CsPasswordWS
        For i = 1 To UBound(vRst)
            For j = 1 To UBound(vRst, 2)
                .Cells(i, j) = vRst(i, j)
            Next j
        Next i
    End With
End Sub
Sub ConnectTxtTimeSeriesData(sTicker As String)
    Dim sPath As String
    sPath = ThisWorkbook.Path
    Call CreateSchemaFileTimeSeriesData(sTicker)
    Set adoCn = New ADODB.Connection
    adoCn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & sPath & ";" & "Extensions=asc,csv,tab,txt;"
End Sub
Function TextTimeSeriesDataImport(sTicker As String, sSQL As String) As Variant
 
    Dim vRst() As Variant
    Dim lRows As Long, iCols As Integer
    Dim lRow As Long, iField As Integer
    Call ConnectTxtTimeSeriesData(sTicker)
 
    Set adoRs = New ADODB.Recordset
 
    With adoRs
        .ActiveConnection = adoCn
        .Source = sSQL
        .CursorLocation = adUseClient
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
 
        lRows = .RecordCount
        iCols = .fields.Count
        ReDim vRst(lRows, iCols) As Variant
        .MoveFirst
        Do While Not .EOF
            lRow = lRow + 1
            For iField = 0 To .fields.Count - 1
                vRst(lRow, iField + 1) = .fields(iField).Value
            Next iField
            .MoveNext
        Loop
    End With
 
    TextTimeSeriesDataImport = vRst()
End Function
Sub CreateSchemaFileTimeSeriesData(sTicker As String)
    Dim Handle As Integer
    Dim Msg As String ' For error handling.
 
    On Local Error GoTo CreateSchemaFile_Err
 
    Handle = FreeFile
 
    Open ThisWorkbook.Path & "\" & "Schema.ini" _
        For Output Access Write As #Handle
 
    Print #Handle, "[" & sTicker & ".txt]"
    Print #Handle, "ColNameHeader = True"
    Print #Handle, "CharacterSet = ANSI"
    Print #Handle, "Format=Delimited(;)"
    Print #Handle, "Format = CSVDelimited"
    Print #Handle, "DecimalSymbol = ."
    Print #Handle, "DateTimeFormat = DD.MM.YYYY"
    Print #Handle, ""
    Print #Handle, "Col1 = Ticker Double"
    Print #Handle, "Col2= dDate Date"
    Print #Handle, "Col3= Open Double"
    Print #Handle, "Col4= High Double"
    Print #Handle, "Col5= Low Double"
    Print #Handle, "Col6= Close Double"
    Print #Handle, "Col7= Volume Double"
 
CreateSchemaFile_End:
        Close Handle
        Exit Sub
 
CreateSchemaFile_Err:
        Msg = "Error #: " & Format$(Err.Number) & vbCrLf
        Msg = Msg & Err.Description
        MsgBox Msg
        Resume CreateSchemaFile_End
End Sub

 

[DianaW]

But you seem to be doing some of the calculations in Access, I would have thought that you just needed Open high low close per day, per ticker? then do all the high-low etc in EXcel as per the link.

Brian

Brian,

as per your question: At the end of the day I would like to have a tool with which I can screen stocks on any date according to certain ATR criteria. And I feel that any query solution is much faster than other array-based solutions. Besides that I also need to learn SQL...

Thanks for your help!

Diana

 

[Brianwarnock]

What I don't understand is that you say in post 1

I have a table which contains stock prices; the columns are Ticker, dDate, Open, High, Low, Close, Volume.

So if this was exported to excel then you can you Excel functions easily
eg to get the max across the columns c to f on row2 =Max(c2:f2)

Excel is designed for calculations, put your data in their and do them.

Brian

 

[Brianwarnock]

Our last posts crossed, ok if you need it to be in ACCESS for long term goals fine, I'll retire its to complex for my old brain.

Brian

 

[DianaW]

Brian,

I appreciate your time and help. And I get your point. One point I forgot to mention: The txt file does not only contain data for one stock but for many more, thus it has >300,000 rows. Thus, we have to make some pre-selection of the data before I can calculate something on a worksheet. And our intention was to solve everything which is possible by this query and only do "the rest" (like finding the maximum of the different columns) on a worksheet / with arrays.

So what I would like to have is a query which gives me for each of the last 20 trading days (the number should be variable later) the figures Open, High, Low, Close and the Close of yesterday (here namliam gave a helpful suggestion) grouped by ticker (i.e. the end result would be 20 x 'number of tickers' rows).

The calculations of High-Low etc., the finding of the max column and averaging these maximums I would then make on a worksheet...

Best,
Diana

 

[namliam]

Or am I making another mistake???

'this works:
'    sSQL = "SELECT * FROM 5401.txt"
 
'this doesn't:
    sSQL = "SELECT Q1.dDate AS dToday, Max(Q2.dDate) AS dYesterday" & _
                " FROM 5401.txt AS Q1, 5401.txt AS Q2" & _
                " WHERE Q2.dDate<Q1.dDate" & _
                " GROUP BY Q1.dDate"
 
    vRst() = TextTimeSeriesDataImport(sTicker, sSQL)
End Sub
Sub ConnectTxtTimeSeriesData(sTicker As String)
    Set adoCn = New ADODB.Connection
    adoCn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & sPath & ";" & "Extensions=asc,csv,tab,txt;"
End Sub

Doesnt look like a mistake... I know this works in a 'real' database (i.e. access) not sure about excel... and/or loading this of a textfile.
Also ADO connections are not my strongest subject (i.e. I NEVER use them)

Also connecting into a textfile... not done that ever before....

The problem may be in the way this is contructed, it is a complete cartasian product, meaning if you have 10 records in your file... the query has to search 10 * 10 records = 100.
You have 300.000 records, thus search 90.000.000.000 records. This puts quite some strain offcourse on you computer. It may just take a LOOOONG while for that all to come together.

A small test on my computer shows that for ~3000 records (I just added a bunch of duplicate dates) this takes 13 seconds...
For the double number of records ~6000, takes roughly 1 minute.

Another way of doing this is using distinct... This for 6000 still only takes ~1 second...
Your source tables would then not be 5401.txt but
( Select distinct dDate from 5401.txt )

Thus reducing the ~6000 (a lot of duplicate) records/dates to only 366
Now 366 * 366 = 133.956
instead of 6000 * 6000 = 36.000.000

offcourse with each consecutive day, this will get slower. If you can make a temporary table to join into this where you would store 'Yesterday' as the intermediate result... Then you only need to find yesterday for today.

But I think the "Distinct" solution above will help you for the next year(s) to come.

Have fun!

 

[Brianwarnock]

Maybe I should just butt out but I don't see where the duplicate records come from. I had assumed data that contained Ticker,date/time, price as the key fields required, and each record would for a ticker would atleast change date/time.
I also would have thought that 5 simple queries would provide the data for the spreadsheet.

Q1 filters data and adds field dDatevalue
q2 uses q1 Group on ticker and dDatevalue, minofdDate,MaxofdDate,High as MaxPrice,Low as Minprice.
q3open Joins q2-q1 on ticker,ddatevalue,minofddate to ddate and selects price as Open
q3close ditto only maxofddate and price as Close
q4 joins q2 to both q3 and select q2.ticker, q2.ddatevalue, q2.high,q2.low, q3open.open,q3close.close
And you now have all the data for the spreadsheet, yesterdays close is easy to pick up on a spreadsheet.

As to performance maybe the "system" that is being created should run q1 as an append query to a table selecting only new data, but I don't know if that is possible or desirable.
It could be that all of the above runs on new data and adds fresh rows to the spreadsheet, but again I don't know if that is desirable. If I were a young programmer that would be the challege I'd like.

Brian

 

[namliam]

Maybe I should just butt out but I don't see where the duplicate records come from.

The query is a cartasian product, meaning it is not joined...

Presume 2 (small) tables
Key1
1
2
3
4
5

Key2
1
2
3
4
5

In a join situation you would get only 5 records... In a cartasian product you join all records with all records...
1-1
1-2
1-3
1-4
1-5
2-1
2-2
2-3
2-4
2-5
3-1
3-2
3-3
3-4
3-5
Etc....

Now the Q2.dDate<Q1.dDate would equate to Key2<Key1 limiting above result to
2-1 << Note, no 1-something!
3-1
3-2
4-1
4-2
4-3
5-1
5-2
5-3
5-4

For which it then gets the maximum value for Key2
2-1
3-2
4-3
5-4

And you now have all the data for the spreadsheet, yesterdays close is easy to pick up on a spreadsheet.

Yes yesterdays close is easy to pick up on the spreadsheet, but in a database a little harder... this trick does it in the database.

Maybe a function would be better than this trickery....
Make a new module, paste below code.

Dim yClose as double
Function GetYClose(thisclose as double) as double
    GetYClose = yClose
    YClose = ThisClose
end function

Now call this function in your query... YesterdayClose: GetYClose([TodaysCloseValue])

Trouble though is that if you run this again the first close value is faulty... it is hard to get this really full proof, unless you maybe include the time or something....

If I were a young programmer that would be the challege I'd like.

Certainly making something that updates incrementaly would be best, performance wize....
Then again making it fully automated (i.e. not get yesterdays close in excel) is a challange as well.
Another option could be to automate getting yesterdays close in Excel itself, making it automated... but easy.

Then again... once these queries are in place... it would work like a charm

More ways than one to skin a cat !