This is really not as difficult as you might think. If the database contains 10 years of price data, where trading only happens Monday-Friday excluding Holidays, and you were able to extract DATE RANGES as SETS from this data, it would not be difficult to determine if the SET is complete or not.
For example, if the DATE RANGE is NOV 05 to FEB 05, you would know you had the complete set of data if you had data leading UP TO NOV 05 and also data following FEB 05. So for example, if the first DATA SET based on the NOV 05 to FEB 05 requirement actually started on NOV 07 because no trading actually occurred on NOV 05 and 06, you'd know this was the case and not that your data simply started in the middle of the SET itself if you had data leading up to NOV 07 to begin with, say NOV 02, 03, 04. And in any case, this issue really is only at the BEGINNING and END of the data where you may get PARTIAL SETS. If the data started somewhere between the DATE RANGE, and the data ended also somewhere in the middle, it would not be difficult to determine this. One other very important fact exists, and that is that stock data, excluding weekends, never stops trading for 3 straight days. So if you are missing more than say 5 days in front of the first DATA SET, as that is where the data actually starts, you'd know it was missing a complete DATA SET for your first SET from this data. Keep in mind that the whole exercise is to extract ONLY the data that falls between two dates selected by the user from all the data available. Each SET (from start date to end date) will eventually be numbered as SET 1, 2, 3, .... Then one can compare all odd sets, even sets, all sets, first 5 sets, last 10 sets, etc. Apples to apples. The 'year' isn't the reference, because if you went from NOV 05 to FEB 05, you'd be going from one year into the next. The data must be in sets from NOV 05 to FEB 05, not the other way around, if that is what the user selects. Hope this makes it clearer. Cheers! Rick #>-----Original Message----- #>From: sqlite-users-boun...@sqlite.org #>[mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Simon Slavin #>Sent: Wednesday, November 11, 2009 4:53 PM #>To: General Discussion of SQLite Database #>Subject: Re: [sqlite] Reverse Referencing Rows #> #> #>On 11 Nov 2009, at 7:12pm, Rick Ratchford wrote: #> #>> To determine if the set is complete, there would clearly be #>data rows #>> PRIOR to the start date and data rows that FOLLOW the end #>date. This #>> is how I'd determine that a set is complete with all available data #>> for those 'sample date windows'. #> #>This makes no sense to me. To determine if I have data for #>each workday within a period I need a definition of which #>days within the period are workdays. Either a table of all #>workdays, or a list of all non-workdays, or some other way of #>determination which is in a form SQL can access. In the #>financial systems I used to work with you'd usually find a #>TABLE which listed each day and it's workday number. #> #>So if the daynumber of today last year was, say, 88,000 the #>daynumber of today might be 88,250. To determine if I had #>data for every day in the last year I'd subtract 88,000 from #>88,250 and then check to see whether I had data for 250 #>different days within the period. #> #>Simon. #>_______________________________________________ #>sqlite-users mailing list #>sqlite-users@sqlite.org #>http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users #> #> _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
