[RESOLVED] Need Help with SQL Statement for SQLite table

[webbiz]

The SQLite MemDB contains a TABLE (TPrices).

It consists of ID (Int Prim Key), Date (ShortDate), Open (double), High (double), Low (double), Close (double), Volume (double), OI (double).

These are stock prices (or futures, forex, etc.) and there is one record for each day.

I'm now at the point of needing to create routines that will convert this DAILY time frame data into WEEKLY, MONTHLY, YEARLY, etc. for display as bars on the chart.

What I need is to create a Rs (cRecordset) for whatever time frame is selected.

So let's say WEEKLY:

The record for WEEKLY should be: ID, Date (Friday's date), Open (first open price of that week), High (highest that week), Low (lowest that week), Close (last closing price that week), Volume (sum of all volume that week), OI (sum of all OI that week).

Due to Holidays, I cannot assume a week has a Monday for the Open value, so it must be the 'first available' open that week.
Same reason, the Close value is the 'last available' close that week.

I'm in need of a SQL statement that can do this conversion and result a Rs (cRecordset) which my existing methods will be able to use just like the original DAILY Rs.

In one of my older apps I had done something like this:

    SQLString = "SELECT date(max(Date), 'weekday 5') as MaxDate, Date, Year, Month, Week, " & _
                "First(Open,ID) as Open, Max(High) as High, " & _
                "Min(Low) as Low, Last(Close,ID) as Close, Week as WeekNum, 0 as IsSwingTop1, " & _
                "0 as IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null as Delta2, " & _
                "0 as Offset1, 0 as Offset2 FROM [" & sTable & "] GROUP By Year, Week HAVING Date < '" & sDateTemp & "'"

But it has all kinds of other stuff in there and after all these years I don't know if it contains what I need. Just want to play it safe and get some feedback as to the best SQL format string to use.

TIA

[webbiz]

I tried this SQL Statement gleaned from parts of my old app statement, just to test, and it generates 1 faulty record.

*Bad Code*

Private Sub optWeekly_Click()
Dim sSQL As String
Dim RsTemp As cRecordset




    sSQL = "SELECT date(max(Date), 'weekday 5') as Date," & _
           "First(Open, ID) as Open," & _
           "MAX(High) AS High, " & _
           "MIN(Low) AS Low, " & _
           "Last(Close, ID) as Close " & _
           "FROM T " & _
           "ORDER BY Date DESC"




    Set RsTemp = MetaStockDS.MemDB.GetRs(sSQL)


    'ucStockChart1.DataSource = RsTemp
    
    Dim x As Long
    
    For x = 0 To RsTemp.RecordCount - 1
        Debug.Print RsTemp.ValueMatrix(x, 1) 'Date
        Debug.Print RsTemp.ValueMatrix(x, 2) 'Open
        Debug.Print RsTemp.ValueMatrix(x, 3) 'High
        Debug.Print RsTemp.ValueMatrix(x, 4) 'Low
        Debug.Print RsTemp.ValueMatrix(x, 5) 'Close
        Debug.Print RsTemp.ValueMatrix(x, 6) 'Vol
        Debug.Print RsTemp.ValueMatrix(x, 7) 'OI
        Debug.Print "----------------------------------------"
        
    
    Next x


End Sub

I'd like to avoid having to do this the easy (but undesirable) way. Since the daily data is already in the Rs recordset, I could loop through it and convert it to weekly via standard VB6 methods and loops, and one by one insert it back into the recordset.

But it would be faster and cleaner (and good education) to do this via SQL statement.

Just having trouble figuring out how to tell SQLite to convert the daily to weekly.

[webbiz]

How do you LOOK at the Table or Recordset contents (for testing) if it is a MemDB?

[jmcilhinney]

This question is about SQL, not VB6, so the thread has been moved to the Database Development forum. If the question wouldn't change if the programming language changed, the question is not about the programming language.

[Zvoni]

Do you have Sample-Data?
What you're trying to achieve is possible, but i need sample data.

As for your "Holiday"-Problem:
Easy: You're looking into a time interval of Monday to Friday, and if the Stock-Exchange is closed on Monday, the first "available" Date is Tuesday.
You just have to know how to find that out.

But to show you i need data

[Zvoni]

Right.
I've created some sample Data
--> ItemID is the ForeignKey to your StockItem, Bond, Share, whatever

CREATE TABLE "T" (
    "ID"    INTEGER,
    "ItemID"    INTEGER,
    "StockDate"    TEXT,
    "Price"    REAL,
    PRIMARY KEY("ID")
);

INSERT INTO "T" ("ID", "ItemID", "StockDate", "Price") VALUES ('1', '1', '2023-08-14', '5.2');
INSERT INTO "T" ("ID", "ItemID", "StockDate", "Price") VALUES ('2', '1', '2023-08-15', '4.89');
INSERT INTO "T" ("ID", "ItemID", "StockDate", "Price") VALUES ('3', '1', '2023-08-16', '4.96');
INSERT INTO "T" ("ID", "ItemID", "StockDate", "Price") VALUES ('4', '1', '2023-08-17', '5.65');
INSERT INTO "T" ("ID", "ItemID", "StockDate", "Price") VALUES ('5', '1', '2023-08-18', '5.45');

/*NOTE: Monday 2023-08-21 is Missing here !!*/
INSERT INTO "T" ("ID", "ItemID", "StockDate", "Price") VALUES ('6', '1', '2023-08-22', '5.52');
INSERT INTO "T" ("ID", "ItemID", "StockDate", "Price") VALUES ('7', '1', '2023-08-23', '4.75');
INSERT INTO "T" ("ID", "ItemID", "StockDate", "Price") VALUES ('8', '1', '2023-08-24', '4.93');
INSERT INTO "T" ("ID", "ItemID", "StockDate", "Price") VALUES ('9', '1', '2023-08-25', '4.84');

Query:

WITH
    /*Note: For testing, here is hardcoded date 2023-08-23. Replace with 'now' */
    DA  AS (SELECT Date('2023-08-23','localtime') As Today),
    TD  AS (SELECT Today, strftime('%W',Today) As WeekNum FROM DA), 
    WK  AS (SELECT WeekNum, date(date(Today,'weekday 5'),'-4 days') As Monday, date(Today,'weekday 5') As Friday FROM TD),
    VA  AS (SELECT strftime('%W',StockDate) As StockWeek, Date(StockDate) As StockDate, Price, ItemID, 
            ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY Date(StockDate)) AS RNSDASC, 
            ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY Date(StockDate) DESC) AS RNSDDESC, 
            ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY Price) AS RNPRASC,
            ROW_NUMBER() OVER(PARTITION BY ItemID ORDER BY Price DESC) AS RNPRDESC
            FROM T INNER JOIN WK ON Date(StockDate) BETWEEN Monday AND Friday)
SELECT DISTINCT WeekNum, VA0.ItemID,
VA1.StockDate As OpenDate, VA1.Price As OpenPrice, 
VA2.StockDate As CloseDate, VA2.Price As ClosePrice, 
VA3.Price As PriceMin,
VA4.Price As PriceMax 
FROM WK    
INNER JOIN VA AS VA0 ON WK.WeekNum=VA0.StockWeek 
LEFT JOIN VA AS VA1 ON VA0.StockWeek=VA1.StockWeek AND VA0.ItemID=VA1.ItemID AND VA1.RNSDASC=1
LEFT JOIN VA AS VA2 ON VA0.StockWeek=VA2.StockWeek AND VA0.ItemID=VA2.ItemID AND VA2.RNSDDESC=1
LEFT JOIN VA AS VA3 ON VA0.StockWeek=VA3.StockWeek AND VA0.ItemID=VA3.ItemID AND VA3.RNPRASC=1
LEFT JOIN VA AS VA4 ON VA0.StockWeek=VA4.StockWeek AND VA0.ItemID=VA4.ItemID AND VA4.RNPRDESC=1
/*WHERE VA0.ItemID=1*/

Returns

WeekNum	ItemID	OpenDate	OpenPrice	CloseDate	ClosePrice	PriceMin	PriceMax
34	1	2023-08-22	5.52	2023-08-25	4.84	4.75	5.52

How does it work?
I'm using CTE's, which are basically temp VIEWs
DA Returns the mentioned Date --> usually with 'now' for current date
TD returns the date and its WeekNum --> comes from DA
WK returns the Date for Monday and Friday of the given WeekNum (which comes from TD)
VA returns the Values From your Table T where StockDate is Between Monday and Friday of the Given WeekNum which comes from WK.

VA has some "specialties":
"StockWeek" is the StockDate as Weeknumber --> using to connect the record to the WeekNum
StockDate, Price and ItemID are Fields in T (self-explanatory)
the 4 ROW_NUMBERS work as follows:
RNSDASC --> RowNumberStockDateAscending --> enumerate the StockDates in ascending Order separated/partitioned by ItemID --> Value 1 returns the First Date of that Week. --> OpenDate
We don't need to partition by Week since we already only grab the Values for that week
RNSDDESC --> RowNumberStockDateDescending --> enumerate the StockDates in descending Order separated/partitioned by ItemID --> Value 1 returns the Last Date of that Week. --> CloseDate
RNPRASC --> RowNumberPRiceAscending --> enumerate the Prices in ascending Order separated/partitioned by ItemID --> Value 1 returns the lowest Price of that Week.
RNPRDESC --> RowNumberPRiceDescending --> enumerate the Prices in descending Order separated/partitioned by ItemID --> Value 1 returns the Highest Price of that Week.

First i take the WeekNum from WK to get the Week in which i'm interested in (Could have also grabed it from TD)
The INNER JOIN returns the ItemID(s) from VA --> In a final WHERE-Clause you could filter on a specific ItemID (see above)
The 4 LEFT JOINS connect to the INNER JOIN to return above mentioned Values

Finally: Yes, i know that DA, TD and WK could be "consolidated" into one Statement, but i like to separate stuff like that, so that i only have to change 1 value instead of 4 or more, since those values propagate "downstairs" to the other Statements. And there is usually no (or not measurable) penalty separating statements, which don't access Tables (or other DB-Objects). I'm just setting up constant Values


EDIT: No idea how "Volume" and "OI" figures into this. Would need to see the Data

EDIT2: The reason why i introduced "ItemID" is: A Microsoft-Share surely has a different price to, say, a Tesla-Share.
OTOH, i really don't know how your data is setup. It just made sense to me

[Zvoni]

How do you LOOK at the Table or Recordset contents (for testing) if it is a MemDB?

Instead of MemDB, use a real DB for testing purposes

[Zvoni]

btw: Let's see, if you can see/figure out, how to do monthly, yearly.....

[webbiz]

Do you have Sample-Data?
What you're trying to achieve is possible, but i need sample data.

As for your "Holiday"-Problem:
Easy: You're looking into a time interval of Monday to Friday, and if the Stock-Exchange is closed on Monday, the first "available" Date is Tuesday.
You just have to know how to find that out.

But to show you i need data

Thanks @Zvoni,

I've attached a CSV file for the purpose of testing. In my test app, the data is in a TABLE called "T" (MemDB). It is pulled into a cRecordset called "Rs" and passed to a .DataSource property in the ucStockChart usercontrol.

Here is the code that loads the stock data into the table "T".

Public Function LoadStockTable(ByVal ZeroBasedMasterIndex As Long) As Boolean
Dim i&, FNr&, FieldCount&, RecordCount&, Cmd As cCommand
Dim Records5() As TSize5, Records6() As TSize6, Records7() As TSize7


On Error GoTo RollBack
MemDB.BeginTrans
  RsMaster.AbsolutePosition = ZeroBasedMasterIndex + 1
  FieldCount = RsMaster!FieldCount.Value
 
  FNr = FreeFile
  Open mImportDir & "F" & RsMaster!ID.Value & ".dat" For Binary As FNr
    RecordCount = LOF(FNr) \ (FieldCount * 4)
    Select Case FieldCount 'handle the different MetaStock-Versions
      Case 5: ReDim Records5(0 To RecordCount - 1): Get FNr, , Records5
      Case 6: ReDim Records6(0 To RecordCount - 1): Get FNr, , Records6
      Case 7: ReDim Records7(0 To RecordCount - 1): Get FNr, , Records7
    End Select
  Close FNr


  mCurrentStock = RsMaster!StockName & " " & RsMaster("StockSymbol")
  mMinValue = 1E+35 'reset the Min-Value
  mMaxValue = -1E+35 'reset the Max-Value
  
  'we have filled in our Arrays - now let's create a table, compatible with all MetaStock-Field-Formats
  MemDB.Exec "Drop Table If Exists T" 'make sure, to drop the current one (if there is any)
  MemDB.Exec "Create Table T(ID Integer Primary Key, Date ShortDate," _
              & "Open Double, High Double, Low Double, Close Double, Volume Double, OI Double)"


  'and now fast inserts over a Cmd-Object, so let's create one
  Set Cmd = MemDB.CreateCommand("Insert Into T Values(?,?,?,?,?,?,?,?)")
  
  For i = 1 To RecordCount - 1 'skip the first record in our Array
 
    Select Case FieldCount 'handle the different MetaStock-Versions
      Case 5
        Cmd.SetShortDate 2, MSingle2VBDate(MReal2Single(Records5(i).mDate))
        Cmd.SetDouble 4, Round(CDbl(MReal2Single(Records5(i).mHigh, True)), 5)
        Cmd.SetDouble 5, Round(CDbl(MReal2Single(Records5(i).mLow, True)), 5)
        Cmd.SetDouble 6, Round(CDbl(MReal2Single(Records5(i).mClose, True)), 5)
        Cmd.SetDouble 7, Round(CDbl(MReal2Single(Records5(i).mVolume)), 5)
      Case 6
        Cmd.SetShortDate 2, MSingle2VBDate(MReal2Single(Records6(i).mDate))
        Cmd.SetDouble 3, Round(CDbl(MReal2Single(Records6(i).mOpen, True)), 5)
        Cmd.SetDouble 4, Round(CDbl(MReal2Single(Records6(i).mHigh, True)), 5)
        Cmd.SetDouble 5, Round(CDbl(MReal2Single(Records6(i).mLow, True)), 5)
        Cmd.SetDouble 6, Round(CDbl(MReal2Single(Records6(i).mClose, True)), 5)
        Cmd.SetDouble 7, Round(CDbl(MReal2Single(Records6(i).mVolume)), 5)
      Case 7
        Cmd.SetShortDate 2, MSingle2VBDate(MReal2Single(Records7(i).mDate))
        Cmd.SetDouble 3, Round(CDbl(MReal2Single(Records7(i).mOpen, True)), 5)
        Cmd.SetDouble 4, Round(CDbl(MReal2Single(Records7(i).mHigh, True)), 5)
        Cmd.SetDouble 5, Round(CDbl(MReal2Single(Records7(i).mLow, True)), 5)
        Cmd.SetDouble 6, Round(CDbl(MReal2Single(Records7(i).mClose, True)), 5)
        Cmd.SetDouble 7, Round(CDbl(MReal2Single(Records7(i).mVolume)), 5)
        Cmd.SetDouble 8, Round(CDbl(MReal2Single(Records7(i).mOI)), 5)
    End Select


    Cmd.Execute
  Next


MemDB.CommitTrans
LoadStockTable = True
Exit Function


RollBack:
MemDB.RollbackTrans
MsgBox "LoadStockTable-Error: " + Err.Description
End Function

SPY.zip


In display price charts in different time-frames, as well as manipulation functions against different time -frames, I figured that having the data in a recordset already converted into those time-frames would be better than having every method contain a "Select-Case" with conversion code.

For the time-frames have the same 'format' in: DATE, OPEN, HIGH, LOW, CLOSE, VOLUME, OI

For the WEEKLY conversion, with every week being (Monday-Friday), the data record for the week should use FRIDAY's date for DATE.

The OPEN is the 'first' Open price for the week. Usually Monday, but if missing due to Holiday then Tuesday, or Wednesday, whichever is first available.

The HIGH is the highest high value for the week. MAX(High).
The LOW is the lowest low value for the week. MIN(Low).

The CLOSE is the opposite of the open. It is the 'last' Close price for the week. This is usually Friday, unless missing due to a Holiday, then Thursday, etc. Note: The week still uses the Friday 'date' for Date regardless if the data for Friday is missing.

The VOLUME is the SUM(Volume) for the week.
The OI is the SUM(OI) for the week.


I got lost in the weeds with the SQL example especially containing data vars not needed. It's quite extensive in size, and it makes me wonder about the code below that I pulled from an old (still in use) app that I have that have conversion methods like this one below:

Public Sub CreateWeekly(Cnn As cConnection, ByVal sTable As String)
'This routine takes a stock TABLE (sTable) of all the DAILY price data and creates
'a new table that contains all WEEKLY price data.
Dim SQLString As String
Dim lngRecords As Long
Dim sDateTemp As String
Dim RsTemp As cRecordset
Dim i As Long
Dim dDate As Date


    SQLString = "SELECT TOP 1 Date FROM [" & sTable & "] ORDER BY Date DESC"
    
    Set RsTemp = Cnn.OpenRecordset(SQLString)
    
    gdLastRecDate = RsTemp.ValueMatrix(0, 0)
    
    
    'First, make sure that gdStartDispDate is a week ending date.
    gdStartDispDate = LastDateInWeek(gdStartDispDate)
    
    sDateTemp = Format$(gdStartDispDate, "yyyy-mm-dd")
    
    If DateValue(sDateTemp) > gdLastRecDate Then
    
        'If the last record in the database does not fall on a Friday, we need
        'to set sDateTemp to Monday so it will load up until previous Friday
        If Weekday(gdLastRecDate) <> vbFriday Then
            sDateTemp = Format$(gdLastRecDate - (Weekday(gdLastRecDate, vbMonday) - 1), "yyyy-mm-dd")
        End If
        
    End If
    
    SQLString = "SELECT date(max(Date), 'weekday 5') as MaxDate, Date, Year, Month, Week, " & _
                "First(Open,ID) as Open, Max(High) as High, " & _
                "Min(Low) as Low, Last(Close,ID) as Close, Week as WeekNum, 0 as IsSwingTop1, " & _
                "0 as IsSwingBtm1, 0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null as Delta2, " & _
                "0 as Offset1, 0 as Offset2 FROM [" & sTable & "] GROUP By Year, Week HAVING Date < '" & sDateTemp & "'"
                
    
    Set Rs = Cnn.OpenRecordset(SQLString)
    lngRecords = Rs.RecordCount
    
    'frmDBTest.FillDBGrid Rs
    
    gdLastRecDate = Rs.ValueMatrix(lngRecords - 1, 0)
    gdStartDispDate = DateAdd("ww", 1, gdLastRecDate)
    frmMain.txtStartDate = gdStartDispDate
    
    frmMain.lblStatus = "Data loaded up to: " & Rs.ValueMatrix(lngRecords - 1, 0)
    frmMain.Caption = "[" & sTable & "] Records loaded = (" & lngRecords & ")"
    
    'Create a MarketData()
    MarketData = Rs.GetRows(, , "Date, Year, Month, Week, Open, High, Low, Close")
    
End Sub

The code above, however, creates a completely different recordset than what I need. It had additional fields and requirements, but included the conversion to weekly. I tried to glean from this but was not successful.

And here is the old MONTHLY conversion method:

Public Sub CreateMonthly(Cnn As cConnection, ByVal sTable As String)
'This routine takes a stock TABLE (sTable) of all the DAILY price data and creates
'a new table that contains all WEEKLY price data.
Dim SQLString As String
Dim lngRecords As Long
Dim sDateTemp As String
Dim RsTemp As cRecordset




    
    SQLString = "SELECT TOP 1 Date FROM [" & sTable & "]  GROUP BY Year, Month ORDER BY Date DESC"
    
    Set RsTemp = Cnn.OpenRecordset(SQLString)
    
    gdLastRecDate = RsTemp.ValueMatrix(0, 0)
    
    gdStartDispDate = LastDateInMonth(gdStartDispDate)
    
    If gdStartDispDate < gdLastRecDate Then
        
        sDateTemp = Format$(gdStartDispDate, "yyyy-mm-dd")
    
    Else
        'Check to see if gdLastRecDate is really a partial month record.
        'If so, we want to make sure not to include it by setting sDateTemp
        'to the first day of the month.
        If Day(gdLastRecDate) < 27 Then 'too small a month
            sDateTemp = Format$(Year(gdLastRecDate) & "-" & Month(gdLastRecDate) & "-01", "yyyy-mm-dd")
        End If
    
    End If
    
    SQLString = "SELECT Date, Year, Month, " & _
                "First(Open,ID) as Open, Max(High) as High, " & _
                "Min(Low) as Low, Last(Close,ID) as Close, 0 as IsSwingTop1, 0 as IsSwingBtm1, " & _
                "0 as IsSwingTop2, 0 as IsSwingBtm2, Null as Delta1, Null as Delta2, " & _
                "0 as Offset1, 0 as Offset2 FROM [" & sTable & "] GROUP By Year, Month HAVING Date < '" & sDateTemp & "'"
    
    Set Rs = Cnn.OpenRecordset(SQLString)
    
    
    lngRecords = Rs.RecordCount
    
    gdLastRecDate = Rs.ValueMatrix(lngRecords - 1, 0)
    gdStartDispDate = ForwardMonth(gdLastRecDate, 1)
    frmMain.txtStartDate = gdStartDispDate
    
    frmMain.lblStatus = "Data loaded up to: " & Rs.ValueMatrix(lngRecords - 1, 0)
    frmMain.Caption = "[" & sTable & "] Records loaded = (" & lngRecords & ")"
    
    
    'frmDBTest.FillDBGrid Rs
    
    'Create a MarketData()
    MarketData = Rs.GetRows(, , "Date, Year, Month, Open, High, Low, Close")


End Sub

Perhaps these old conversion methods provide CLUES as to what I need to do the standard DATE, OPEN, HIGH, LOW, CLOSE, VOLUME, OI recordsets (without all the other frilly fields)?

I will make some changes to my test code to create a hard file DB for testing purposes, but in the end, prefer the MemDB for faster execution.

Thanks.

[webbiz]

Using the AI (it's a crutch, but I need some kind of starting point or guidance with SQL stuff), this is what it suggests:

SELECT 
    MAX(DATE) AS DATE,
    MIN(OPEN) AS OPEN,
    MAX(HIGH) AS HIGH,
    MIN(LOW) AS LOW,
    LAST(CLOSE) AS CLOSE,
    SUM(VOLUME) AS VOLUME,
    SUM(OI) AS OI
FROM (
    SELECT 
        DATE(MAX(DATE), '-6 days') AS DATE,
        strftime('%W', DATE) AS WEEK_NUMBER,
        OPEN,
        HIGH,
        LOW,
        CLOSE,
        VOLUME,
        OI
    FROM TPrices
    GROUP BY WEEK_NUMBER
)
GROUP BY WEEK_NUMBER;

This Max(Date) thing is confusing. If it returns the highest date, how does it know to limit itself to any given Monday-Friday range? Certainly we don't want the latest date from the whole database.

The DATE function takes a Date and one or more 'modifiers'.

The problem I see here is that the date it is given is MAX(DATE), which I do not see how that date is limited to just a single week's sample of dates. Is this AI whack?

There does not appear to be a "start of week" modifier available in SQLite Date().

The weekday modifier ('weekday 1') represents Monday and ('weekday 5') represents Friday. I would think this could be used someway to split the data into weeks? The only thing is that some Monday's and some Friday's will be missing from the data due to Holidays.

This is totally beyond beginner SQL stuff.

[techgnome]

It's grouping by Week_Number ... which is the week of the year... so the first week of the year is 1... the next is 2 .. and on down hte line to 52/53 (depending on how things fall) ... so the Max(Date) of week 1 is say Jan 5... or Jan 7 ...

-tg

[Zvoni]

Oh boy.
You're doing yourself no favor, by NOT saving the Dates as ISO Format.

Quick Routine to converto from US-Format to ISO-Format
DON'T USE "DATE" AS FIELDNAME!! Date ist also a Function in SQLite, so it's a ticking Time-Bomb!!

btw: For importing the CSV, i'd use a Staging-Table "Import" or something.
After the Dates has been converted, you psh the further into your real Table

UPDATE SPY  /* Or better the Staging Table */
SET StockDate=substr(substr(substr(StockDate,Instr(StockDate,'/')+1), 1),Instr(StockDate,'/')+1)||'-'||
substr(StockDate,1,Instr(StockDate,'/')-1)||'-'||
substr(substr(StockDate,Instr(StockDate,'/')+1), 1,Instr(StockDate,'/')-1)

Query:

WITH
    /*Note: For testing, here is hardcoded date. Replace with 'now' or whatever */
    DA  AS (SELECT Date('2023-07-04','localtime') As Today),
    TD  AS (SELECT Today, strftime('%W',Today) As WeekNum FROM DA), 
    WK  AS (SELECT WeekNum, date(date(Today,'weekday 5'),'-4 days') As Monday, date(Today,'weekday 5') As Friday FROM TD),
    VA  AS (SELECT strftime('%W',StockDate) As StockWeek, Date(StockDate) As StockDate, 
            Open, High, Low, Close, Volume, OI, 
            ROW_NUMBER() OVER(ORDER BY Date(StockDate)) AS RNOPEN, 
            ROW_NUMBER() OVER(ORDER BY Date(StockDate) DESC) AS RNCLOSE, 
            ROW_NUMBER() OVER(ORDER BY Low) AS RNLOW,
            ROW_NUMBER() OVER(ORDER BY High DESC) AS RNHIGH
            FROM SPY INNER JOIN WK ON Date(StockDate) BETWEEN Monday AND Friday
            )
SELECT DISTINCT WeekNum, 
VA1.Open, VA2.Close, VA3.Low, VA4.High,
SUM(VA0.Volume) AS Volume,
SUM(VA0.OI) AS OI 
FROM WK    
INNER JOIN VA AS VA0 ON WK.WeekNum=VA0.StockWeek 
LEFT JOIN VA AS VA1 ON VA0.StockWeek=VA1.StockWeek AND VA1.RNOPEN=1
LEFT JOIN VA AS VA2 ON VA0.StockWeek=VA2.StockWeek AND VA2.RNCLOSE=1
LEFT JOIN VA AS VA3 ON VA0.StockWeek=VA3.StockWeek AND VA3.RNLOW=1
LEFT JOIN VA AS VA4 ON VA0.StockWeek=VA4.StockWeek AND VA4.RNHIGH=1 
GROUP BY WeekNum, VA1.Open, VA2.Close, VA3.Low, VA4.High

Raw Data in your Table (Note the Missing Holiday July 4th)

StockDate	Open	High	Low	Close	Volume	OI
2023-07-03	442.92001	444.07999	442.63	443.79001	32793378	444
2023-07-05	441.91	443.88901	441.89999	443.13	58418432	443
2023-07-06	439.42001	440.10001	437.06	439.66	80658304	440
2023-07-07	438.63	442.64001	438.29999	438.54999	86134176	439

Query returns

WeekNum	Open	Close	Low	High	Volume	OI
27	442.92001	438.54999	437.06	444.07999	258004290	1766

[webbiz]

Oh sorry, I posted this while Zvoni was replying to a previous post. This post is NOT a response to that. I'm looking at it now.

Here's some education Zvoni provided me some time ago.

Code:
SELECT strftime('%Y-%W','2023-01-31') '--> Returns 2023-05 - Week 5 in 2023
SELECT strftime('%Y-%W',YourDateField) '--> Returns YYYY-WW of Your Date
Select additional Fields. Don't Forget to aggregate (averages, sums, Min, Max etc.) and GROUP BY
Code:
SELECTstrftime('%Y-%W',YourDateField) As InWeek,
Avg(Open) As AvgOpen,
Avg(Close) As AvgClose,
Max(High) As MaxHigh,
Min(Low) As MinLow
From SomeTable
/*Optional: WHERE YourDateField BETWEEN SomeStartDate AND SomeEndDate*/
GROUP BY strftime('%Y-%W',YourDateField)
ORDER BY strftime('%Y-%W',YourDateField)

strftime() takes a format, in this case the Year and Week?

The next field is the timestring, in this case it would be 'Date', the field name.

and the Year/Week of the Date is placed in 'InWeek'.

The following commands like Avg(Open) As AvgOpen, etc. are they based solely on the data within 'InWeek'? Is 'InWeek' Sunday to Saturday? Of course I don't want an Avg for Open, but the first open value for the week. Is that First_Value(Open)?

From TPrices

When you GROUP BY strftime('%Y-%W',YourDateField)..

..is this how it is made into a single record? GROUP BY strftime('%Y-%W', Date)

ORDER BY strftime('%Y-%W',YourDateField)

Is this a 'sort' command? Is it ascending by default?

ORDER BY strftime('%Y-%W', Date)

So if I want create the recordset where record #1 is actually the latest date and the rest is going back in time, it would be...

ORDER BY strftime('%Y-%W', Date) DESC

[webbiz]

*delete* remove. error.

[webbiz]

It's grouping by Week_Number ... which is the week of the year... so the first week of the year is 1... the next is 2 .. and on down hte line to 52/53 (depending on how things fall) ... so the Max(Date) of week 1 is say Jan 5... or Jan 7 ...

-tg

Thank you.

[webbiz]

Oh boy.
You're doing yourself no favor, by NOT saving the Dates as ISO Format.

Quick Routine to converto from US-Format to ISO-Format
DON'T USE "DATE" AS FIELDNAME!! Date ist also a Function in SQLite, so it's a ticking Time-Bomb!!

btw: For importing the CSV, i'd use a Staging-Table "Import" or something.
After the Dates has been converted, you psh the further into your real Table

UPDATE SPY  /* Or better the Staging Table */
SET StockDate=substr(substr(substr(StockDate,Instr(StockDate,'/')+1), 1),Instr(StockDate,'/')+1)||'-'||
substr(StockDate,1,Instr(StockDate,'/')-1)||'-'||
substr(substr(StockDate,Instr(StockDate,'/')+1), 1,Instr(StockDate,'/')-1)

Query:

WITH
    /*Note: For testing, here is hardcoded date. Replace with 'now' or whatever */
    DA  AS (SELECT Date('2023-07-04','localtime') As Today),
    TD  AS (SELECT Today, strftime('%W',Today) As WeekNum FROM DA), 
    WK  AS (SELECT WeekNum, date(date(Today,'weekday 5'),'-4 days') As Monday, date(Today,'weekday 5') As Friday FROM TD),
    VA  AS (SELECT strftime('%W',StockDate) As StockWeek, Date(StockDate) As StockDate, 
            Open, High, Low, Close, Volume, OI, 
            ROW_NUMBER() OVER(ORDER BY Date(StockDate)) AS RNOPEN, 
            ROW_NUMBER() OVER(ORDER BY Date(StockDate) DESC) AS RNCLOSE, 
            ROW_NUMBER() OVER(ORDER BY Low) AS RNLOW,
            ROW_NUMBER() OVER(ORDER BY High DESC) AS RNHIGH
            FROM SPY INNER JOIN WK ON Date(StockDate) BETWEEN Monday AND Friday
            )
SELECT DISTINCT WeekNum, 
VA1.Open, VA2.Close, VA3.Low, VA4.High,
SUM(VA0.Volume) AS Volume,
SUM(VA0.OI) AS OI 
FROM WK    
INNER JOIN VA AS VA0 ON WK.WeekNum=VA0.StockWeek 
LEFT JOIN VA AS VA1 ON VA0.StockWeek=VA1.StockWeek AND VA1.RNOPEN=1
LEFT JOIN VA AS VA2 ON VA0.StockWeek=VA2.StockWeek AND VA2.RNCLOSE=1
LEFT JOIN VA AS VA3 ON VA0.StockWeek=VA3.StockWeek AND VA3.RNLOW=1
LEFT JOIN VA AS VA4 ON VA0.StockWeek=VA4.StockWeek AND VA4.RNHIGH=1 
GROUP BY WeekNum, VA1.Open, VA2.Close, VA3.Low, VA4.High

Raw Data in your Table (Note the Missing Holiday July 4th)

StockDate	Open	High	Low	Close	Volume	OI
2023-07-03	442.92001	444.07999	442.63	443.79001	32793378	444
2023-07-05	441.91	443.88901	441.89999	443.13	58418432	443
2023-07-06	439.42001	440.10001	437.06	439.66	80658304	440
2023-07-07	438.63	442.64001	438.29999	438.54999	86134176	439

Query returns

WeekNum	Open	Close	Low	High	Volume	OI
27	442.92001	438.54999	437.06	444.07999	258004290	1766

Thank you @Zvoni for your assistance and recommendations. I'll take a look at how the Date is raw in the original Metastock and see about making those ISO adjustments.

FYI, the data is loaded into the test app from a Metastock formatted file. The CSV was just provided as you mentioned the need for 'test data'. The Test app itself will not be loading in CSV data.

I note that the Metastock methods being used for DATE are originally in ShortDate format.

  MemDB.Exec "Create Table T(ID Integer Primary Key, Date ShortDate," _
              & "Open Double, High Double, Low Double, Close Double, Volume Double, OI Double)"

It's an easy thing to change the 'Date' fieldname throughout the code. I'll make the adjustment.

Cmd.SetShortDate 2, MSingle2VBDate(MReal2Single(Records7(i).mDate))

As to the 'ShortDate', I'm going to have to study this more to find the ISO equivalent without having to do the 'date conversion' thing you mentioned. Better to pull the date out directly from Metastock as ISO and avoid the extra steps. I believe Metastock format is already ISO and it is the code I'm using that is turning it into 'shortdate' formation.

I'll get back shortly after a lengthy study of your comments here AND making some adjustments.

Again, thanks!

EDIT: Concerning ISO. Note the Cmd.SetShortDate() which is a RC6/SQLite method. I don't see any Set ISO type method and wondering if by a different name.
Cmd.SetDate? Might that accept the date 'as is', leaving me to simply change the 'type' in the Create Table SQL statement?

[Zvoni]

And here the Query to View the whole Year by Week:

WITH
    /* RW is a recursive CTE returning a Number-Sequence of 1 to 54 --> Possible WeekNumbers */
    RW    AS (SELECT 1 As WeekNum UNION ALL SELECT WeekNum+1 FROM RW WHERE WeekNum+1<=54),   
    WD(DayNum, WeekDay) AS (VALUES('0', 'Sunday'), ('1', 'Monday'), ('2','Tuesday'),('3','Wednesday'),('4','Thursday'),('5','Friday'),('6','Saturday')),
    VA    AS (SELECT strftime('%W',StockDate) As StockWeek, Date(StockDate) As StockDate, Open, High, Low, Close, Volume, OI, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%W',StockDate) ORDER BY Date(StockDate)) AS RNOPEN, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%W',StockDate) ORDER BY Date(StockDate) DESC) AS RNCLOSE, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%W',StockDate) ORDER BY Low) AS RNLOW,
            ROW_NUMBER() OVER(PARTITION BY strftime('%W',StockDate) ORDER BY High DESC) AS RNHIGH
            FROM SPY 
            )            
SELECT Format('%02d',WeekNum) As WeekNum, 
VA1.Open, VA2.Close, VA3.Low, VA4.High, WDO.WeekDay||' - '||VA1.StockDate As OpenDate, WDC.WeekDay||' - '||VA2.StockDate As CloseDate, 
SUM(VA0.Volume) AS Volume,
SUM(VA0.OI) AS OI 
FROM RW 
INNER JOIN VA AS VA0 ON Format('%02d',RW.WeekNum)=VA0.StockWeek 
LEFT JOIN VA AS VA1 ON VA0.StockWeek=VA1.StockWeek AND VA1.RNOPEN=1
INNER JOIN WD AS WDO ON WDO.DayNum=strftime('%w',VA1.StockDate)
LEFT JOIN VA AS VA2 ON VA0.StockWeek=VA2.StockWeek AND VA2.RNCLOSE=1
INNER JOIN WD AS WDC ON WDC.DayNum=strftime('%w',VA2.StockDate)
LEFT JOIN VA AS VA3 ON VA0.StockWeek=VA3.StockWeek AND VA3.RNLOW=1
LEFT JOIN VA AS VA4 ON VA0.StockWeek=VA4.StockWeek AND VA4.RNHIGH=1 
GROUP BY WeekNum, VA1.Open, VA2.Close, VA3.Low, VA4.High, WDO.WeekDay||' - '||VA1.StockDate,WDC.WeekDay||' - '||VA2.StockDate
ORDER BY WeekNum

Returns (just copied the first 10 weeks)

WeekNum	Open	Close	Low	High	OpenDate	CloseDate	Volume	OI
01	384.37	388.07999	377.83099	389.25	Tuesday - 2023-01-03	Friday - 2023-01-06	341944880	1532
02	390.37	398.5	386.26999	399.10001	Monday - 2023-01-09	Friday - 2023-01-13	362278876	1971
03	398.48001	395.88	387.26001	400.23001	Tuesday - 2023-01-17	Friday - 2023-01-20	341074816	1574
04	396.72	405.67999	393.56	408.16	Monday - 2023-01-23	Friday - 2023-01-27	369137560	2012
05	402.79999	412.35001	400.28	418.31	Monday - 2023-01-30	Friday - 2023-02-03	458864200	2047
06	409.79001	408.04001	405.01001	416.48999	Monday - 2023-02-06	Friday - 2023-02-10	376978100	2051
07	408.72	407.26001	404.04999	415.04999	Monday - 2023-02-13	Friday - 2023-02-17	380677420	2055
08	403.06	396.38	393.64001	404.16	Tuesday - 2023-02-21	Friday - 2023-02-24	370835032	1595
09	399.87	404.19	392.32999	404.45001	Monday - 2023-02-27	Friday - 2023-03-03	452114808	1991
10	405.04999	385.91	384.32001	407.45001	Monday - 2023-03-06	Friday - 2023-03-10	557051480	1979

Note: I added the actual open/close DATES for proof

[techgnome]

The type of the field would be just "Date" it doesn't have a format ... what DOES have a format is your data being imported... ideally the date should be in an ISO format - specifically the "yyyy-mm-dd" format ... "2023-05-06" ... there's no ambiguity as to what the date is... as opposed to the common "05/06/2023" or "06/05/2023" ... so the format issue is really about the source data, not so much the database.


-tg

[webbiz]

Side Note:

Metastock formatted data uses their own unique date format. They are single integer values starting from a specific base date (e.g. January 1, 1900).

I have functions that convert it from a REAL value to a SINGLE value, then it is converted to a VB Date using this method:

Private Function MSingle2VBDate(SD As Single, Optional Y&, Optional M&, Optional D&, Optional Q&) As Date
  Dim L As Long: L = SD 'convert to a long first


  Y = IIf(L > 999999, 2000, 1900) + L \ 10000 Mod 100
  M = L \ 100 Mod 100 'set the month
  D = L Mod 100 'set the day
  Q = M \ 4 + 1 'set the quartal
  
  MSingle2VBDate = DateSerial(Y, M, D)
End Function

I could easily create a new Function MSingle2ISO() and using a copy of the above, just change the internals to produce a YYYY-MM-DD string and return that.

As for the 'table' creation, the type 'ShortDate' does not appear to be a standard SQL type but the SQLite.org site shows "TEXT" as the type. Since I can't find a reference to 'ShortDate' in SQL, I don't know if this is a RC6 thing or not.

  MemDB.Exec "Create Table T(ID Integer Primary Key, isoDate Text," _
              & "Open Double, High Double, Low Double, Close Double, Volume Double, OI Double)"

I know this is not a VB6 forum or a RC6 forum, but the moderator moved my question here as I had yet to get to the VB parts of this thread.

[webbiz]

The type of the field would be just "Date" it doesn't have a format ... what DOES have a format is your data being imported... ideally the date should be in an ISO format - specifically the "yyyy-mm-dd" format ... "2023-05-06" ... there's no ambiguity as to what the date is... as opposed to the common "05/06/2023" or "06/05/2023" ... so the format issue is really about the source data, not so much the database.


-tg

I mentioned this in the previous while you were posting this. So it's out of order.

The Metastock date format is a tad funky, but I have Functions to convert it. So I'll convert before pushing the data into the Table.

As for my app displaying and using 'ShortDate' format (which it should), I'll do the converting 'outside' the Table. Users of the app will not want to look at ISO fomat but the ShortDate format for their region.

Thanks.

[Zvoni]

I mentioned this in the previous while you were posting this. So it's out of order.

The Metastock date format is a tad funky, but I have Functions to convert it. So I'll convert before pushing the data into the Table.

As for my app displaying and using 'ShortDate' format (which it should), I'll do the converting 'outside' the Table. Users of the app will not want to look at ISO fomat but the ShortDate format for their region.

Thanks.

always remember there is a difference between how something is stored and how something is displayed
Store as it is recommended, display in whatever format you want

EDIT: i noticed a bug in my last query.
don‘t sort (ORDER BY Clause) by WeekNum, but by OpenDate or CloseDate

[webbiz]

always remember there is a difference between how something is stored and how something is displayed
Store as it is recommended, display in whatever format you want

EDIT: i noticed a bug in my last query.
don‘t sort (ORDER BY Clause) by WeekNum, but by OpenDate or CloseDate

Changing the Date format was easy.

  MemDB.Exec "Create Table T(ID Integer Primary Key, Date Text," _
              & "Open Double, High Double, Low Double, Close Double, Volume Double, OI Double)"

Cmd.SetDate 2, MSingle2ISODate(MReal2Single(Records7(i).mDate))

Private Function MSingle2ISODate(SD As Single, Optional Y&, Optional M&, Optional D&, Optional Q&) As Date
  Dim L As Long: L = SD 'convert to a long first
  Dim isoDate As String
  Dim dateValue As Date
  
  Y = IIf(L > 999999, 2000, 1900) + L \ 10000 Mod 100
  M = L \ 100 Mod 100 'set the month
  D = L Mod 100 'set the day
  Q = M \ 4 + 1 'set the quartal
  
  dateValue = DateSerial(Y, M, D)
  
  MSingle2ISODate = Format(dateValue, "yyyy-MM-dd")


End Function

It's all in ISO now.

Funny thing is that the app was displaying ISO all along, but this was due to a couple of Format$(D, "yyyy-mm-dd") code lines.

[webbiz]

Private Sub optWeekly_Click()
Dim sSQL As String


    
'    sSQL = "SELECT " & _
'            "ROW_NUMBER() OVER (ORDER BY strftime('%Y-%W', tDate) DESC) AS ID, " & _
'            "strftime('%Y-%W',tDate) As tDate, " & _
'            "First(Open,ID) as Open, Max(High) as High, " & _
'            "Min(Low) as Low, Last(Close,ID) as Close, " & _
'            "Sum(Volume) as Volume, Sum(OI) as OI FROM T GROUP BY strftime('%Y-%W',tDate) ORDER BY strftime('%Y-%W',tDate) Desc"


    sSQL = "SELECT " & _
           "ROW_NUMBER() OVER (ORDER BY strftime('%Y-%W', tDate) DESC) AS ID, " & _
           "strftime('%Y-%W', tDate, 'start of week +4 days') As tDate, " & _
           "First(Open, ID) as Open, Max(High) as High, " & _
           "Min(Low) as Low, Last(Close, ID) as Close, " & _
           "Sum(Volume) as Volume, Sum(OI) as OI " & _
           "FROM T " & _
           "GROUP BY strftime('%Y-%W', tDate) " & _
           "ORDER BY strftime('%Y-%W', tDate) DESC"


    
    Set ucStockChart1.DataSource = MetaStockDS.MemDB.GetRs(sSQL)
    
    ucStockChart1.DisplayTest


End Sub

The above code that is commented out produced the correct values but not the correct date format. Instead, it listed the dates as YYYY-WW where WW = week number.

1 2023-35 0.6411 0.6527 0.6406 0.6486 268062 667421
2 2023-34 0.6412 0.6493 0.6385 0.6424 368671 1127730
3 2023-33 0.6504 0.6529 0.6371 0.6411 411716 1046687
4 2023-32 0.658 0.6626 0.6492 0.6501 338956 913800
5 2023-31 0.6661 0.6751 0.6524 0.6582 456011 841403
6 2023-30 0.6743 0.6833 0.6633 0.6663 473790 719333
7 2023-29 0.6851 0.686 0.6734 0.6743 401700 735487
8 2023-28 0.6706 0.691 0.6638 0.6854 467998 758541
9 2023-27 0.6677 0.6721 0.6614 0.671 382526 616793
10 2023-26 0.669 0.6736 0.661 0.6678 445671 699873

I searched around for some possible ways to change this to the ISO date instead for the FRIDAY of the week.

The uncommented code above was my latest attempt. Unfortunately the tDate field is BLANK after running this. No date at all!

1 0.6411 0.6527 0.6406 0.6486 268062 667421
2 0.6412 0.6493 0.6385 0.6424 368671 1127730
3 0.6504 0.6529 0.6371 0.6411 411716 1046687
4 0.658 0.6626 0.6492 0.6501 338956 913800
5 0.6661 0.6751 0.6524 0.6582 456011 841403
6 0.6743 0.6833 0.6633 0.6663 473790 719333
7 0.6851 0.686 0.6734 0.6743 401700 735487
8 0.6706 0.691 0.6638 0.6854 467998 758541
9 0.6677 0.6721 0.6614 0.671 382526 616793
10 0.669 0.6736 0.661 0.6678 445671 699873

I'll keep searching and experimenting. But if someone can spot the issue I'd really appreciate it.

TIA

[Zvoni]

The uncommented code above was my latest attempt. Unfortunately the tDate field is BLANK after running this. No date at all!

Your Problem is this line:
"strftime('%Y-%W', tDate, 'start of week +4 days') As tDate, " & _

There is no "start of the week"-Modifier.
What i don't understand: Why are you so interested in a Friday?
You should be intersted in "Last Day of Business-Week"
e.g. the Week before Easter Holidays.
Good Friday is a Holiday, so last Day of Business is Thursday

[Zvoni]

I still don't understand, why you're going through this convoluted mess, since i've shown you, how it works

I've reduced some complexity

Weekly view for the Year

WITH
    WD(DayNum, WeekDay) AS (VALUES('0', 'Sunday'), ('1', 'Monday'), ('2','Tuesday'),('3','Wednesday'),('4','Thursday'),('5','Friday'),('6','Saturday')),
    VA AS (SELECT strftime('%Y-%W',StockDate) As Interval, Date(StockDate) As StockDate, Open, High, Low, Close, Volume, OI, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%W',StockDate) ORDER BY Date(StockDate)) AS RNOPEN, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%W',StockDate) ORDER BY Date(StockDate) DESC) AS RNCLOSE, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%W',StockDate) ORDER BY Low) AS RNLOW,
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%W',StockDate) ORDER BY High DESC) AS RNHIGH
            FROM SPY)            
SELECT VA0.Interval, 
WDO.WeekDay||' - '||VA1.StockDate As OpenDate, 
WDC.WeekDay||' - '||VA2.StockDate As CloseDate, 
VA1.Open, VA2.Close, VA3.Low, VA4.High, 
SUM(VA0.Volume) AS Volume, SUM(VA0.OI) AS OI
FROM VA AS VA0 
LEFT JOIN VA AS VA1 ON VA0.Interval=VA1.Interval AND VA1.RNOPEN=1
INNER JOIN WD AS WDO ON WDO.DayNum=strftime('%w',VA1.StockDate)
LEFT JOIN VA AS VA2 ON VA0.Interval=VA2.Interval AND VA2.RNCLOSE=1
INNER JOIN WD AS WDC ON WDC.DayNum=strftime('%w',VA2.StockDate)
LEFT JOIN VA AS VA3 ON VA0.Interval=VA3.Interval AND VA3.RNLOW=1
LEFT JOIN VA AS VA4 ON VA0.Interval=VA4.Interval AND VA4.RNHIGH=1 
GROUP BY VA0.Interval, VA1.Open, VA2.Close, VA3.Low, VA4.High, WDO.WeekDay||' - '||VA1.StockDate,WDC.WeekDay||' - '||VA2.StockDate
ORDER BY VA1.StockDate

Returns (i've included the week before Easter for you to see Closing Date being the Thursday)

Interval	OpenDate	CloseDate	Open	Close	Low	High	Volume	OI
2022-52	Thursday - 2022-12-29	Friday - 2022-12-30	379.63	382.42999	378.42999	384.35001	150993084	765
2023-01	Tuesday - 2023-01-03	Friday - 2023-01-06	384.37	388.07999	377.83099	389.25	341944880	1532
2023-02	Monday - 2023-01-09	Friday - 2023-01-13	390.37	398.5	386.26999	399.10001	362278876	1971
2023-03	Tuesday - 2023-01-17	Friday - 2023-01-20	398.48001	395.88	387.26001	400.23001	341074816	1574
2023-04	Monday - 2023-01-23	Friday - 2023-01-27	396.72	405.67999	393.56	408.16	369137560	2012
2023-05	Monday - 2023-01-30	Friday - 2023-02-03	402.79999	412.35001	400.28	418.31	458864200	2047
2023-06	Monday - 2023-02-06	Friday - 2023-02-10	409.79001	408.04001	405.01001	416.48999	376978100	2051
2023-07	Monday - 2023-02-13	Friday - 2023-02-17	408.72	407.26001	404.04999	415.04999	380677420	2055
2023-08	Tuesday - 2023-02-21	Friday - 2023-02-24	403.06	396.38	393.64001	404.16	370835032	1595
2023-09	Monday - 2023-02-27	Friday - 2023-03-03	399.87	404.19	392.32999	404.45001	452114808	1991
2023-10	Monday - 2023-03-06	Friday - 2023-03-10	405.04999	385.91	384.32001	407.45001	557051480	1979
2023-11	Monday - 2023-03-13	Friday - 2023-03-17	381.81	389.98999	380.64999	396.47	764846592	1952
2023-12	Monday - 2023-03-20	Friday - 2023-03-24	390.79999	395.75	389.39999	402.48999	523448064	1974
2023-13	Monday - 2023-03-27	Friday - 2023-03-31	398.12	409.39001	393.69	409.70001	396282632	2006
2023-14	Monday - 2023-04-03	Thursday - 2023-04-06	408.85001	409.19	405.67801	411.92001	262936220	1637
2023-15	Monday - 2023-04-10	Friday - 2023-04-14	406.60999	412.45999	405.97	415.09	373375616	2053
2023-16	Monday - 2023-04-17	Friday - 2023-04-21	412.37	412.20001	410.17001	415.72	334521508	2066

Monthly View for the Year

WITH
    WD(DayNum, WeekDay) AS (VALUES('0', 'Sunday'), ('1', 'Monday'), ('2','Tuesday'),('3','Wednesday'),('4','Thursday'),('5','Friday'),('6','Saturday')),
    VA AS (SELECT strftime('%Y-%m',StockDate) As Interval, Date(StockDate) As StockDate, Open, High, Low, Close, Volume, OI, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%m',StockDate) ORDER BY Date(StockDate)) AS RNOPEN, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%m',StockDate) ORDER BY Date(StockDate) DESC) AS RNCLOSE, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%m',StockDate) ORDER BY Low) AS RNLOW,
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%m',StockDate) ORDER BY High DESC) AS RNHIGH
            FROM SPY)            
SELECT VA0.Interval, 
WDO.WeekDay||' - '||VA1.StockDate As OpenDate, 
WDC.WeekDay||' - '||VA2.StockDate As CloseDate, 
VA1.Open, VA2.Close, VA3.Low, VA4.High, 
SUM(VA0.Volume) AS Volume, SUM(VA0.OI) AS OI
FROM VA AS VA0 
LEFT JOIN VA AS VA1 ON VA0.Interval=VA1.Interval AND VA1.RNOPEN=1
INNER JOIN WD AS WDO ON WDO.DayNum=strftime('%w',VA1.StockDate)
LEFT JOIN VA AS VA2 ON VA0.Interval=VA2.Interval AND VA2.RNCLOSE=1
INNER JOIN WD AS WDC ON WDC.DayNum=strftime('%w',VA2.StockDate)
LEFT JOIN VA AS VA3 ON VA0.Interval=VA3.Interval AND VA3.RNLOW=1
LEFT JOIN VA AS VA4 ON VA0.Interval=VA4.Interval AND VA4.RNHIGH=1 
GROUP BY VA0.Interval, VA1.Open, VA2.Close, VA3.Low, VA4.High, WDO.WeekDay||' - '||VA1.StockDate,WDC.WeekDay||' - '||VA2.StockDate
ORDER BY VA1.StockDate

Returns

Interval	OpenDate	CloseDate	Open	Close	Low	High	Volume	OI
2022-12	Thursday - 2022-12-29	Friday - 2022-12-30	379.63	382.42999	378.42999	384.35001	150993084	765
2023-01	Tuesday - 2023-01-03	Tuesday - 2023-01-31	384.37	406.48001	377.83099	408.16	1575449932	7896
2023-02	Wednesday - 2023-02-01	Tuesday - 2023-02-28	405.211	396.26001	393.64001	418.31	1603224256	7735
2023-03	Wednesday - 2023-03-01	Friday - 2023-03-31	395.41	409.39001	380.64999	409.70001	2516860272	9108
2023-04	Monday - 2023-04-03	Friday - 2023-04-28	408.85001	415.92999	403.78	415.94	1395780712	7807
2023-05	Monday - 2023-05-01	Wednesday - 2023-05-31	415.47	417.85001	403.73999	422.57999	1780908832	9107
2023-06	Thursday - 2023-06-01	Friday - 2023-06-30	418.09	443.28	416.79001	444.29999	1754814072	9107
2023-07	Monday - 2023-07-03	Monday - 2023-07-31	442.92001	457.79001	437.06	459.44	1374774874	8989
2023-08	Tuesday - 2023-08-01	Friday - 2023-08-25	456.26999	439.97	433.01001	457.25	1475499608	8441

Just replace the Field and Table-Name (What i call StockDate, SPY and so on)

[Zvoni]

Weekly View of the Year, including "FridayoftheWeek"

WITH
    WD(DayNum, WeekDay) AS (VALUES('0', 'Sunday'), ('1', 'Monday'), ('2','Tuesday'),('3','Wednesday'),('4','Thursday'),('5','Friday'),('6','Saturday')),
    VA AS (SELECT strftime('%Y-%W',StockDate) As Interval, Date(StockDate) As StockDate, 
            Date(StockDate,'+'||(5-CAST(Trim(strftime('%w', Date(StockDate))) AS INTEGER))||' days') As FridayOfTheWeek,
            Open, High, Low, Close, Volume, OI, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%W',StockDate) ORDER BY Date(StockDate)) AS RNOPEN, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%W',StockDate) ORDER BY Date(StockDate) DESC) AS RNCLOSE, 
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%W',StockDate) ORDER BY Low) AS RNLOW,
            ROW_NUMBER() OVER(PARTITION BY strftime('%Y-%W',StockDate) ORDER BY High DESC) AS RNHIGH
            FROM SPY)            
SELECT VA0.Interval, 
WDF.WeekDay||' - '||VA0.FridayOfTheWeek as FridayOfTheWeek,
WDO.WeekDay||' - '||VA1.StockDate As OpenDate, 
WDC.WeekDay||' - '||VA2.StockDate As CloseDate, 
VA1.Open, VA2.Close, VA3.Low, VA4.High, 
SUM(VA0.Volume) AS Volume, SUM(VA0.OI) AS OI
FROM VA AS VA0 
INNER JOIN WD AS WDF ON WDF.DayNum=strftime('%w',VA0.FridayOfTheWeek)
LEFT JOIN VA AS VA1 ON VA0.Interval=VA1.Interval AND VA1.RNOPEN=1
INNER JOIN WD AS WDO ON WDO.DayNum=strftime('%w',VA1.StockDate)
LEFT JOIN VA AS VA2 ON VA0.Interval=VA2.Interval AND VA2.RNCLOSE=1
INNER JOIN WD AS WDC ON WDC.DayNum=strftime('%w',VA2.StockDate)
LEFT JOIN VA AS VA3 ON VA0.Interval=VA3.Interval AND VA3.RNLOW=1
LEFT JOIN VA AS VA4 ON VA0.Interval=VA4.Interval AND VA4.RNHIGH=1 
GROUP BY VA0.Interval, WDF.WeekDay||' - '||VA0.FridayOfTheWeek, 
WDO.WeekDay||' - '||VA1.StockDate,WDC.WeekDay||' - '||VA2.StockDate, 
VA1.Open, VA2.Close, VA3.Low, VA4.High
ORDER BY VA0.FridayOfTheWeek

Returns

Interval	FridayOfTheWeek	OpenDate	CloseDate	Open	Close	Low	High	Volume	OI
2022-52	Friday - 2022-12-30	Thursday - 2022-12-29	Friday - 2022-12-30	379.63	382.42999	378.42999	384.35001	150993084	765
2023-01	Friday - 2023-01-06	Tuesday - 2023-01-03	Friday - 2023-01-06	384.37	388.07999	377.83099	389.25	341944880	1532
2023-02	Friday - 2023-01-13	Monday - 2023-01-09	Friday - 2023-01-13	390.37	398.5	386.26999	399.10001	362278876	1971
2023-03	Friday - 2023-01-20	Tuesday - 2023-01-17	Friday - 2023-01-20	398.48001	395.88	387.26001	400.23001	341074816	1574
2023-04	Friday - 2023-01-27	Monday - 2023-01-23	Friday - 2023-01-27	396.72	405.67999	393.56	408.16	369137560	2012
2023-05	Friday - 2023-02-03	Monday - 2023-01-30	Friday - 2023-02-03	402.79999	412.35001	400.28	418.31	458864200	2047
2023-06	Friday - 2023-02-10	Monday - 2023-02-06	Friday - 2023-02-10	409.79001	408.04001	405.01001	416.48999	376978100	2051
2023-07	Friday - 2023-02-17	Monday - 2023-02-13	Friday - 2023-02-17	408.72	407.26001	404.04999	415.04999	380677420	2055
2023-08	Friday - 2023-02-24	Tuesday - 2023-02-21	Friday - 2023-02-24	403.06	396.38	393.64001	404.16	370835032	1595
2023-09	Friday - 2023-03-03	Monday - 2023-02-27	Friday - 2023-03-03	399.87	404.19	392.32999	404.45001	452114808	1991
2023-10	Friday - 2023-03-10	Monday - 2023-03-06	Friday - 2023-03-10	405.04999	385.91	384.32001	407.45001	557051480	1979
2023-11	Friday - 2023-03-17	Monday - 2023-03-13	Friday - 2023-03-17	381.81	389.98999	380.64999	396.47	764846592	1952
2023-12	Friday - 2023-03-24	Monday - 2023-03-20	Friday - 2023-03-24	390.79999	395.75	389.39999	402.48999	523448064	1974
2023-13	Friday - 2023-03-31	Monday - 2023-03-27	Friday - 2023-03-31	398.12	409.39001	393.69	409.70001	396282632	2006
2023-14	Friday - 2023-04-07	Monday - 2023-04-03	Thursday - 2023-04-06	408.85001	409.19	405.67801	411.92001	262936220	1637
2023-15	Friday - 2023-04-14	Monday - 2023-04-10	Friday - 2023-04-14	406.60999	412.45999	405.97	415.09	373375616	2053

[webbiz]

Thanks @Zvoni. It's nice to see the variations in the results.

I'm a tad overwhelmed, to be honest. I'm looking at the columns and noting that the FridayOfTheWeek is the part I need to extract from this information, change it so it does not say "Friday -", and leave only the ISO date in the set.

As I'm dealing with the RC6/SQLite methods, I'm trying to figure out what chunks of what you have demonstrated needs to be used and what is not needed right now.

This part here appears to be what I should focus on?

Date(StockDate,'+'||(5-CAST(Trim(strftime('%w', Date(StockDate))) AS INTEGER))||' days') As FridayOfTheWeek

I saw that "5-CAST()" and for a minute was searching for "5-CAST()" as if that was the function name. LOL! I promise, I did not hit the ground hard the last time I jumped. ;-)

Okay, staring over. 5 minus CAST().

SQLite CAST operator:
The CAST operator is used to convert a value from a data type to another data type.
For example, if you have a numeric value stored as a string value like this ” ‘12.5’ ” and you want to convert it to be a numeric value you can use the CAST operator to do this like this “CAST( ‘12.5’ AS REAL)“. Or if you have a decimal value like 12.5, and you need to get the integer part only, you can cast it to an integer like this “CAST(12.5 AS INTEGER)”.

I'm doing all this while watching the stock market prices which is very distracting. Both require my focus, so I'll look deep into this shortly after the market action slows down a bit.

Thank you!

[Zvoni]

.... that the FridayOfTheWeek is the part I need to extract from this information, change it so it does not say "Friday -", and leave only the ISO date in the set.

......
SELECT VA0.Interval, 
VA0.FridayOfTheWeek as FridayOfTheWeek, /* WDF.WeekDay||' - '|| removed */
WDO.WeekDay||' - '||VA1.StockDate As OpenDate, 
WDC.WeekDay||' - '||VA2.StockDate As CloseDate, 
VA1.Open, VA2.Close, VA3.Low, VA4.High, 
SUM(VA0.Volume) AS Volume, SUM(VA0.OI) AS OI
FROM VA AS VA0 
/* Remove this Inner Join: INNER JOIN WD AS WDF ON WDF.DayNum=strftime('%w',VA0.FridayOfTheWeek) */
LEFT JOIN VA AS VA1 ON VA0.Interval=VA1.Interval AND VA1.RNOPEN=1
INNER JOIN WD AS WDO ON WDO.DayNum=strftime('%w',VA1.StockDate)
LEFT JOIN VA AS VA2 ON VA0.Interval=VA2.Interval AND VA2.RNCLOSE=1
INNER JOIN WD AS WDC ON WDC.DayNum=strftime('%w',VA2.StockDate)
.....
GROUP BY VA0.Interval, VA0.FridayOfTheWeek, /* WDF.WeekDay||' - '|| removed */
WDO.WeekDay||' - '||VA1.StockDate,WDC.WeekDay||' - '||VA2.StockDate, 
VA1.Open, VA2.Close, VA3.Low, VA4.High

As I'm dealing with the RC6/SQLite methods, I'm trying to figure out what chunks of what you have demonstrated needs to be used and what is not needed right now.

This part here appears to be what I should focus on?



I saw that "5-CAST()" and for a minute was searching for "5-CAST()" as if that was the function name. LOL! I promise, I did not hit the ground hard the last time I jumped. ;-)

Okay, staring over. 5 minus CAST().



I'm doing all this while watching the stock market prices which is very distracting. Both require my focus, so I'll look deep into this shortly after the market action slows down a bit.

Thank you!

Date(StockDate,'+'||(5-CAST(Trim(strftime('%w', Date(StockDate))) AS INTEGER))||' days')

Take the Date, and return the WeekDay-Number (Sunday=0), Trim any whitespace, then Cast it to Integer.
Take 5 and subtract that Integer
Construct the Text '+1 days' from it --> Today is Tursday, WeekDay-Number is 4, 5-4 is 1, construct '+1 days'
If your Date is a Tuesday, Weekday is 2, 5-2 is 3, results in '+3 days', Tuesday +3 Days is Friday
and so on
Basically: For each record of each day you have, i "calculate" the Distance to the next Friday

And i don't understand what RC6-Methods have to do with all that
EDIT: Or do you mean, because you want to pass Parameters, and Set them like in your other thread?

EDIT2: and what do you mean with chunks? You need everything from my query, and exactly in that form (CTE, ROWNUMBER etc.)

[webbiz]

......
SELECT VA0.Interval, 
VA0.FridayOfTheWeek as FridayOfTheWeek, /* WDF.WeekDay||' - '|| removed */
WDO.WeekDay||' - '||VA1.StockDate As OpenDate, 
WDC.WeekDay||' - '||VA2.StockDate As CloseDate, 
VA1.Open, VA2.Close, VA3.Low, VA4.High, 
SUM(VA0.Volume) AS Volume, SUM(VA0.OI) AS OI
FROM VA AS VA0 
/* Remove this Inner Join: INNER JOIN WD AS WDF ON WDF.DayNum=strftime('%w',VA0.FridayOfTheWeek) */
LEFT JOIN VA AS VA1 ON VA0.Interval=VA1.Interval AND VA1.RNOPEN=1
INNER JOIN WD AS WDO ON WDO.DayNum=strftime('%w',VA1.StockDate)
LEFT JOIN VA AS VA2 ON VA0.Interval=VA2.Interval AND VA2.RNCLOSE=1
INNER JOIN WD AS WDC ON WDC.DayNum=strftime('%w',VA2.StockDate)
.....
GROUP BY VA0.Interval, VA0.FridayOfTheWeek, /* WDF.WeekDay||' - '|| removed */
WDO.WeekDay||' - '||VA1.StockDate,WDC.WeekDay||' - '||VA2.StockDate, 
VA1.Open, VA2.Close, VA3.Low, VA4.High

Date(StockDate,'+'||(5-CAST(Trim(strftime('%w', Date(StockDate))) AS INTEGER))||' days')

Take the Date, and return the WeekDay-Number (Sunday=0), Trim any whitespace, then Cast it to Integer.
Take 5 and subtract that Integer
Construct the Text '+1 days' from it --> Today is Tursday, WeekDay-Number is 4, 5-4 is 1, construct '+1 days'
If your Date is a Tuesday, Weekday is 2, 5-2 is 3, results in '+3 days', Tuesday +3 Days is Friday
and so on
Basically: For each record of each day you have, i "calculate" the Distance to the next Friday

And i don't understand what RC6-Methods have to do with all that
EDIT: Or do you mean, because you want to pass Parameters, and Set them like in your other thread?

Thanks for the response. I was going to return later with my attempt to figure this out. But I'll take whatever I can get!

And i don't understand what RC6-Methods have to do with all that
EDIT: Or do you mean, because you want to pass Parameters, and Set them like in your other thread?

I would have preferred to keep it all in the one thread, but the moderator moved this here although I knew it was going to flow into the VB6 RC6/SQLite discussion.

I'm using Olaf's SQLite wrapper (RC6) and am not fully versed on the nuances between straight SQLite and the requirements of his functions/methods.

Because during my learning (as best I could) SQL (it's not been easy and I'm time restrained) it has been said that SQL has it's differences across different DB's.

So when I look at an SQL statement such as your examples, I don't know what 'variant' of SQL you are writing.

Not a problem, as I just do a search on the command and see how it is in SQLite.

But that's what I meant by RC6-Methods. The SQLite wrapper methods.

Thanks for this additional information. I will take a deeper dive later today. Gotta make my daily bread today in the SP500. :-)

[webbiz]

*deleted pointless AI gibberish*

[webbiz]

Oh this is really cool. Okay, so that 'Date' line did the trick. Prices and dates are confirmed correct (shown below) for the Australian Dollar Futures (weekly) chart.

The price bars show up on the chart (but the scale is all messed up, so I'll get on it late tonight. should be easy to figure out.)

1 2023-09-01 0.6411 0.6527 0.6406 0.6486 268062 667421
2 2023-08-25 0.6412 0.6493 0.6385 0.6424 368671 1127730
3 2023-08-18 0.6504 0.6529 0.6371 0.6411 411716 1046687
4 2023-08-11 0.658 0.6626 0.6492 0.6501 338956 913800
5 2023-08-04 0.6661 0.6751 0.6524 0.6582 456011 841403
6 2023-07-28 0.6743 0.6833 0.6633 0.6663 473790 719333
7 2023-07-21 0.6851 0.686 0.6734 0.6743 401700 735487
8 2023-07-14 0.6706 0.691 0.6638 0.6854 467998 758541
9 2023-07-07 0.6677 0.6721 0.6614 0.671 382526 616793
10 2023-06-30 0.669 0.6736 0.661 0.6678 445671 699873

Here's the modified code:

Private Sub optWeekly_Click()
Dim sSQL As String


    
    sSQL = "SELECT " & _
            "ROW_NUMBER() OVER (ORDER BY strftime('%Y-%W', tDate) DESC) AS ID, " & _
            "Date(tDate,'+'||(5-CAST(Trim(strftime('%w', Date(tDate))) AS INTEGER))||' days') as tDate, " & _
            "First(Open,ID) as Open, Max(High) as High, " & _
            "Min(Low) as Low, Last(Close,ID) as Close, " & _
            "Sum(Volume) as Volume, Sum(OI) as OI FROM T GROUP BY strftime('%Y-%W',tDate) ORDER BY strftime('%Y-%W',tDate) Desc"


    
    Set ucStockChart1.DataSource = MetaStockDS.MemDB.GetRs(sSQL)
    
    'ucStockChart1.DisplayTest  bunch of Debug.Prints because I've yet to find a quicker test yet.


End Sub

Thanks so much @zvoni. I should be able to use what you've taught me and do the Monthly, Yearly, etc. etc.

[webbiz]

What i don't understand: Why are you so interested in a Friday?
You should be intersted in "Last Day of Business-Week"
e.g. the Week before Easter Holidays.
Good Friday is a Holiday, so last Day of Business is Thursday

This is a resolved thread, but I just realized I did not address this question.

@Zvoni

The reason for FRIDAY is that stock chart applications that show price bars have dates for those bars. In the daily time-frame, each bar is identified by the date the bar represents.

But how does it do this for a weekly time-frame, where each bar represents a complete 5-day trading week?

Some apps use the first day of the week (Monday), while other apps use the last day of the week (Friday). My app uses the last day of the week.

Thus, when talking about a particular week in trading, we might say "week-ending 9/16" to identify the trading week in question.

:-)

[Zvoni]

So when I look at an SQL statement such as your examples, I don't know what 'variant' of SQL you are writing.

No "variant" whatsoever. Official to SQLite conforming SQL.
No "tweaking" by outside "wrappers" needed.