Re: Counting the Dates
- From: "Webbiz" <noreply@xxxxxxx>
- Date: Tue, 29 Jan 2008 17:40:23 -0600
"Ralph" <nt_consulting64@xxxxxxxxx> wrote in message
news:eTe66LRYIHA.1188@xxxxxxxxxxxxxxxxxxxxxxx
"Webbiz" <noreply@xxxxxxx> wrote in message
news:uWSlV$JYIHA.2268@xxxxxxxxxxxxxxxxxxxxxxx
I'm trying to solve a problem in the most efficient way. I can solve this1,
problem, but my solution is turtle slow.
Say you have a file with 10 years of numerical values. One value per
calendar date, excluding weekends and holidays.
January 1, 1997 = 89
January 2, 1997 = 123
January 3, 1997 = 14
...
December 12, 2007 = 77
The first calendar date of the data file does not necessarily start with
January 1st of whatever year. It's likely not.
So you start reading the data file, adding up all the values that fall on
the same Month and Day of each year that you have in the file.
When done, you should have an array that has the total value for January
2, 3, 4, 5, 6, 7... all the way to December 31.Array(365)
In other words, if I have 10 years of data and my data file starts with
March 3rd 1997, I start by placing the value for March 3rd in an array
element, then the 4th, 5th, etc. When I get back to March 3rd of the next
year, this is added to the first March 3rd value and stored in the same
element number of the array.
So to start off with, I imagine I start by creating an array with 366
elements, one for every day of the year that includes the leap year?
I assume that I'd want to use Array(0) for all the January 1 and
for all the December 31?determine
I'm curious if there is a simple way to read the date and quickly
what the array element would be.file
Would I perhaps first create some sort of table reference, perhaps in a
or by way of function, that uses MM/DD as the index and references
numbers
from 0 to 365, so that each date that is read in would be compared to the
indexes of this table, the element number discovered, and then I'd know
where in my array to add the value to?
Am I making this more complicated than it should be? If you say no, I'd
be
very surprised. :-o
Any suggestions or examples?
Thank you.
Webbiz
(In case I'm not making sense: I want to add up all the values found
under
January 1 of each year and place them in the first array element. All the
values found under January 2nd of each year and place the total in the
second array element, etc.)
I'm likely missing something again, but I think I would load the text file
into a database. Then run rules against it creating associated tables.
At that point one could engineer queries to mine the data - "Give me the
total for January 1st."; Give me the total for February."; "
If one needed 'arrays' for processing elsewhere, just have the query
return
one with GetRows().
-ralph
In my application, the data has already been loaded into a DataArray()
structure to be used for all sorts of other things. So it is no longer
needed to be loaded again from the data file.
The DataArray() structure breaks it down into:
Date
Open Price
High Price
Low Price
Closing Price
While the date format from the data file starts out as YYYYMMDD, in the
DataArray() is it already stored as a ShortDate. For the US, that would be
MM/DD/YYYY format.
So going through this DataArray() from 0 to UBound, I figured to read each
date through a For Next and capture the Closing Price. For every 01/01 (Jan
01), add them all up. For all the 01/02, add them all up, etc. And of
course, store the totals in a separate location so that I can reference them
based on the dates, such as "Give me the total for 06/05". How many years
totaled up does not matter.
Come to think of it, I may need to store more information than just the
total.
I'm also thinking of doing this: Instead of totalling the values, I simply
add "1" if it was an up day, and subtract "1" if it is a down day. The final
result will either be a positive value or negative value. I'd also need to
keep track of HOW MANY were added together, so I can calculate a PERCENTAGE.
For example, if I have 10 up days and 5 down days, the final result should
be +5 and 66.6%.
Would I create a Structured Array for this, or simply an additional array?
Thanks.
Webbiz
.
- References:
- Counting the Dates
- From: Webbiz
- Re: Counting the Dates
- From: Ralph
- Counting the Dates
- Prev by Date: Re: Can this be automated in the VBE?
- Next by Date: Re: Getting confused about += and simular
- Previous by thread: Re: Counting the Dates
- Next by thread: Re: Counting the Dates
- Index(es):
Relevant Pages
|
