asked on
Which database can do this search?
I'm currently using FileMaker to store historical stock market index quotes. I like FM because I don't have to get involved with designing an interface at a low level. It also experts and imports text ASCII files easily. Importing data to update/add to existing data works nicely. It suites my needs for most queries but some it doesn't.
Some of the queries I need to run are:
1.) Show all days in SP500 that rallied 50pts or more.
2.) Show previous day (-1 day)of each result that also rallied 50pts or more.
3.) Show -2 day that was negative by 20 pts.
The above queries starts with #1 then builds on that to get #2 and the same to get #3. One result from the above query might look like:
4/5=51pts, 4/4=56pts, 4/3=-23pts
But there may be several similar 3 day results but in different time periods (months/years). Just meaning, it may bring back 10 results.
Another type of query is the above plus bring back +1 day. So the above result would look like:
4/6=30pts, 4/5=51pts, 4/4=56pts, 4/3=-23pts
Even though 4/6 doesn't match the pt criteria, it is the day after 4/5.
Which software will do the above queries but still have some of the FileMaker characteristics mentioned above? I'm fine with some low level programming.
Some of the queries I need to run are:
1.) Show all days in SP500 that rallied 50pts or more.
2.) Show previous day (-1 day)of each result that also rallied 50pts or more.
3.) Show -2 day that was negative by 20 pts.
The above queries starts with #1 then builds on that to get #2 and the same to get #3. One result from the above query might look like:
4/5=51pts, 4/4=56pts, 4/3=-23pts
But there may be several similar 3 day results but in different time periods (months/years). Just meaning, it may bring back 10 results.
Another type of query is the above plus bring back +1 day. So the above result would look like:
4/6=30pts, 4/5=51pts, 4/4=56pts, 4/3=-23pts
Even though 4/6 doesn't match the pt criteria, it is the day after 4/5.
Which software will do the above queries but still have some of the FileMaker characteristics mentioned above? I'm fine with some low level programming.
Oracle DatabaseMicrosoft SQL Server 2005FileMaker Pro
Last Comment
Will Loving
ASKER
I'm not sure what you are asking. FM is a database package similar to Access. They are stored there.
I should think you could do all of these in FM. It might require a little scripting and some secondary relationships but I think it's doable. The stock market is not my forte and I don't know how your data is stored. Do you have a "rally" field or are you determining that from other fields, and by rally, I'm assuming you mean a day in which the index lost ground from an initial high and then recovered at least 50 points?
From the standpoint of being able to determine a previous day's change, you can create a self-join relationship from one day to the previous, by adding a calculation date field called "StockDatePrevious" = StockDate - 1 and then create a relationship from StockDatePrevious to StockDate. This will let you look back one day to the previous days values.
From the standpoint of being able to determine a previous day's change, you can create a self-join relationship from one day to the previous, by adding a calculation date field called "StockDatePrevious" = StockDate - 1 and then create a relationship from StockDatePrevious to StockDate. This will let you look back one day to the previous days values.
ASKER
@willmcn:
I don't have a definition for rally. I'd just be looking for particular point moves.
In regards to the script you're suggesting:
If my main table is HistoricalIndex, I would go into FM's Relationships and add HistoricalIndex2. Then add StockDatePrevious as the calculation you mention above? Then create the relationship to it?
When I query, I should be querying HistoricalIndex2 table?
If I want to get -2 days as well, I'd create another calculation field named StockDatePrevious2:
StockDate - 2
And I could also do -3, -4, etc following the same pattern?
For those other previous days, do any additional relationships need to be defined?
I don't have a definition for rally. I'd just be looking for particular point moves.
In regards to the script you're suggesting:
If my main table is HistoricalIndex, I would go into FM's Relationships and add HistoricalIndex2. Then add StockDatePrevious as the calculation you mention above? Then create the relationship to it?
When I query, I should be querying HistoricalIndex2 table?
If I want to get -2 days as well, I'd create another calculation field named StockDatePrevious2:
StockDate - 2
And I could also do -3, -4, etc following the same pattern?
For those other previous days, do any additional relationships need to be defined?
Regarding the rally definition: Assuming my understanding of a "rally" is correct and if you don't have a field that indicates the amount of any rally, then I assume you must have data points for specific time periods (hourly?) across the day that you are comparing to determine if a rally occurred. Again, I need to make sure my understanding of 1) what a rally is and 2) how you are determining it is correct. As I understand it, a rally is not an increase in points from the previous day, it's an increase in points after having decreased from a high on the same day.
The relationship needs to be from StockDatePrevious in HistoricalIndex to StockDate in HistoricalIndex 2, not the other way around. I also recommend that you use something other than FileMaker's default name for your tables, something that means what it is. I would rename HistoricalIndex 2 to "HistoricalIndex_SJ_Prev1"
Yes you could make multiple relationships that look back 2, 3, 4, 5, etc days creating additional PreviousDay calculations field to calculate the date for each of those, though you're calculation is going to have to be more than just -1, -2 -3 etc because it needs to take into account days that no trading was done. Instead of using the date, if your historical records are serialized (or you add a serial number) you can just calculated the SerialNumber - 1. Then you wouldn't have to worry about dates though you could pull the date via the relationship for your results.
If you want to be able to look back and infinite number of days, then you start to get into recursive relationships which can be both challenging to understand and setup but is ultimately more efficient. If you only need a limited number of Previous or Next days then you can do it the way you've described without getting into that.
The relationship needs to be from StockDatePrevious in HistoricalIndex to StockDate in HistoricalIndex 2, not the other way around. I also recommend that you use something other than FileMaker's default name for your tables, something that means what it is. I would rename HistoricalIndex 2 to "HistoricalIndex_SJ_Prev1"
Yes you could make multiple relationships that look back 2, 3, 4, 5, etc days creating additional PreviousDay calculations field to calculate the date for each of those, though you're calculation is going to have to be more than just -1, -2 -3 etc because it needs to take into account days that no trading was done. Instead of using the date, if your historical records are serialized (or you add a serial number) you can just calculated the SerialNumber - 1. Then you wouldn't have to worry about dates though you could pull the date via the relationship for your results.
If you want to be able to look back and infinite number of days, then you start to get into recursive relationships which can be both challenging to understand and setup but is ultimately more efficient. If you only need a limited number of Previous or Next days then you can do it the way you've described without getting into that.
ASKER
I already do have a self join with HistoricalIndex that joins on Date in both tables. These are used to calculate the current day's gap (current day open - prev day close). But the relationship for StockDatePrevious should be a new self join? Since I may be adding -2, -3, etc fields as well?
How does this work with the query? Once I do a search, it seems I will only see the immediate rows relevant to the search criteria and not rows containing -1, -2, -3 days back. So if my results return three rows and I only have the StockDatePrevious (-1 day) field implemented at that time, I should see 10 results and not 5. That make sense?
How does this work with the query? Once I do a search, it seems I will only see the immediate rows relevant to the search criteria and not rows containing -1, -2, -3 days back. So if my results return three rows and I only have the StockDatePrevious (-1 day) field implemented at that time, I should see 10 results and not 5. That make sense?
I'm trying to understand your query in my head without seeing the database or data, so bear with me (or upload a sample file).
If you are going to create multiple relationships - and again I emphasize that using the Date won't work unless you configure the PreviousDate calculation field to bypass all non-trading days - then when you do the "Find" (using FM's terminology) you can search not only in the local record but in any related record as well. So for example, you can search in the HistoricalIndex record, the current table, for a date range and =50, and then at the same time, in the same "Find Request" also enter search for values such as =50 in the previous day via the relationship.
If you are going to create multiple relationships - and again I emphasize that using the Date won't work unless you configure the PreviousDate calculation field to bypass all non-trading days - then when you do the "Find" (using FM's terminology) you can search not only in the local record but in any related record as well. So for example, you can search in the HistoricalIndex record, the current table, for a date range and =50, and then at the same time, in the same "Find Request" also enter search for values such as =50 in the previous day via the relationship.
ASKER
I'm not really understanding how this search works. To provide an example, let's say my HistoricalIndex table has only these rows/columns:
StockDate, PreviousStockDate, open, close, Range
4/21/2005, , 960, 655, -5
4/22/2005, 4/21/2005, 970, 961, -9
4/23/2005, 4/22/2005, 974, 969, -5
4/24/2005, 4/23/2005, 971, 985, +14
4/11/2008, , 1010, 1015, +5
4/12//2008, 11/11/2008, 1009, 1016, +7
4/13/2008, 1/12/2008, 1020, 1031, +11
9/5/2009, , 1090, 1096, +6
9/6/2009, 9/5/2009, 1091, 1099, +8
9/7/2009, 9/6/2009, 1095, 1075, -20
Now I do this query:
StockDate = 4/*/*
Range >=10
That should return:
4/23/2005, 4/22/2005, 974, 969, -5
4/24/2005, 4/23/2005, 971, 985, +14
4/12//2008, 11/11/2008, 1009, 1016, +7
4/13/2008, 1/12/2008, 1020, 1031, +11
StockDates 4/24/2005 and 4/13/2008 match the Find criteria. And I also get day-1 returned (rows 4/12/2008 and 4/24/2005) even though they do not match the criteria.
If I do this query:
Range <= -20
That should return:
9/6/2009, 9/5/2009, 1091, 1099, +8
9/7/2009, 9/6/2009, 1095, 1075, -20
Row 9/7/2009 matches my Find criteria. I also get day-1 with row 9/6/2009, which does not match the Find criteria.
So day-1 probably will not match the Find criteria but I want day-1 returned in the results as above. Eventually it may return day-2, day-3, which also probably will not match the Find criteria. day-1, day-2, etc being returned in dependent on one row matching the criteria. Previous days just come along for the ride.
StockDate, PreviousStockDate, open, close, Range
4/21/2005, , 960, 655, -5
4/22/2005, 4/21/2005, 970, 961, -9
4/23/2005, 4/22/2005, 974, 969, -5
4/24/2005, 4/23/2005, 971, 985, +14
4/11/2008, , 1010, 1015, +5
4/12//2008, 11/11/2008, 1009, 1016, +7
4/13/2008, 1/12/2008, 1020, 1031, +11
9/5/2009, , 1090, 1096, +6
9/6/2009, 9/5/2009, 1091, 1099, +8
9/7/2009, 9/6/2009, 1095, 1075, -20
Now I do this query:
StockDate = 4/*/*
Range >=10
That should return:
4/23/2005, 4/22/2005, 974, 969, -5
4/24/2005, 4/23/2005, 971, 985, +14
4/12//2008, 11/11/2008, 1009, 1016, +7
4/13/2008, 1/12/2008, 1020, 1031, +11
StockDates 4/24/2005 and 4/13/2008 match the Find criteria. And I also get day-1 returned (rows 4/12/2008 and 4/24/2005) even though they do not match the criteria.
If I do this query:
Range <= -20
That should return:
9/6/2009, 9/5/2009, 1091, 1099, +8
9/7/2009, 9/6/2009, 1095, 1075, -20
Row 9/7/2009 matches my Find criteria. I also get day-1 with row 9/6/2009, which does not match the Find criteria.
So day-1 probably will not match the Find criteria but I want day-1 returned in the results as above. Eventually it may return day-2, day-3, which also probably will not match the Find criteria. day-1, day-2, etc being returned in dependent on one row matching the criteria. Previous days just come along for the ride.
OK, let's start with the first step, the Find Request. Please confirm that you are entering this data as a single Find request in FileMaker, and that the Range field is set to Type: Number. If you want to upload a sample of your Historical Index data, that would also be helpful.
ASKER
Using the attached data, if I query:
Date = 3/*/*
Range < 15
I should get:
3/10/2004 2004 ES H 1139.50 1141.75 1119.50 1120.25 930475 -19.25
3/11/2004 2004 ES M 1119.50 1125.25 1103.25 1104.25 1036107 -15.25
and
3/18/2004 2004 ES M 1122.50 1126.50 1111.50 1122.75 837275 0.25
3/19/2004 2004 ES M 1123.00 1127.25 1106.50 1107.25 649991 -15.75
3/19 and 3/11 match the Find criteria and 3/18, 3/10 display because they are the days before (day -1).
If I do this Find:
Date = 5/*/*
Range < 17
I should get:
5/6/2004 2004 ES M 1122.00 1122.25 1104.75 1113.00 782294 -9
5/7/2004 2004 ES M 1113.00 1117.00 1094.50 1095.50 986625 -17.5
5/7 is the Find match and 5/6 the day before.
I could also do the above with note date criteria. I would just get more results in that case. Hope that helps. 2004-ES-Daily-test-data.csv
Date = 3/*/*
Range < 15
I should get:
3/10/2004 2004 ES H 1139.50 1141.75 1119.50 1120.25 930475 -19.25
3/11/2004 2004 ES M 1119.50 1125.25 1103.25 1104.25 1036107 -15.25
and
3/18/2004 2004 ES M 1122.50 1126.50 1111.50 1122.75 837275 0.25
3/19/2004 2004 ES M 1123.00 1127.25 1106.50 1107.25 649991 -15.75
3/19 and 3/11 match the Find criteria and 3/18, 3/10 display because they are the days before (day -1).
If I do this Find:
Date = 5/*/*
Range < 17
I should get:
5/6/2004 2004 ES M 1122.00 1122.25 1104.75 1113.00 782294 -9
5/7/2004 2004 ES M 1113.00 1117.00 1094.50 1095.50 986625 -17.5
5/7 is the Find match and 5/6 the day before.
I could also do the above with note date criteria. I would just get more results in that case. Hope that helps. 2004-ES-Daily-test-data.csv
ASKER
Also, yes - it is a single Find request and Range is a number.
Found your Find problem. You are attempting to use the asterisk wildcard symbol in a date field, which FileMaker does not support. See: http://help.filemaker.com/app/answers/detail/a_id/4239
To get a range such as you are looking for with "3/*/*" you would use: the search criteria of "3/1/2004...3/31/2004"
When I use that search with the sample data you gave me, I get the correct results. If you need to have wildcards in the years to Find records in March across multiple years. you can try the workaround suggested in the article of converting to text. The article is a little out of date in that it references the "DateToText()" function which is now GetAsDate()
(And in a almost completely unrelated aside note, a few years ago a FileMaker developer created T-shirts with various FM functions emblazoned on them. The ones that said "GetAsDate()" were very popular with the female developers and sold out quickly. I have one that says "NotIsEmpty()" )
The wildcard symbols "*" and "@" cannot be used when performing a find on a number or date field.
To get a range such as you are looking for with "3/*/*" you would use: the search criteria of "3/1/2004...3/31/2004"
When I use that search with the sample data you gave me, I get the correct results. If you need to have wildcards in the years to Find records in March across multiple years. you can try the workaround suggested in the article of converting to text. The article is a little out of date in that it references the "DateToText()" function which is now GetAsDate()
(And in a almost completely unrelated aside note, a few years ago a FileMaker developer created T-shirts with various FM functions emblazoned on them. The ones that said "GetAsDate()" were very popular with the female developers and sold out quickly. I have one that says "NotIsEmpty()" )
ASKER
I use the wildcard and it works fine. But that isn't the issue.
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Ok, that's pretty cool. You have laid out part of the next three previous days horizontally. That's neat to show that it can work but isn't practical. But you understand the idea. With 70 columns, I can't put the next 3 previous days horizontally like that. It would be 280 columns wide. Previous days really need to stack vertically so they look like a row of results. Do you think that is possible?
Not sure what you mean by 70 columns...your data only has 8 columns or 9 if you count the serial number I added.
Can't do any more tonight. I still don't fully understand the search you are going for, so I'll need to go back and re-read. I believe we solved the date issue. If you can clarify what you mean by 70 columns, that would be helpful
Can't do any more tonight. I still don't fully understand the search you are going for, so I'll need to go back and re-read. I believe we solved the date issue. If you can clarify what you mean by 70 columns, that would be helpful
ASKER
The data I uploaded is only a sample and not the real set. The sample has 8 columns. My real set has 70 (more data points). Thanks again for the help.
So are you wanting the ability to query on all columns (fields) back "n" days?
You don't necessarily have to have all columns visible to do the a Find, but the kind and depth of the searches will affect how much relational structure you need to be able to reach the data you want. I think you need to define/clarify the parameters of your searches: how many columns, how many days forward or back and what conditions. I'm sure you can do what you want in FM, but the question is developing a relational structure that will support it.
You don't necessarily have to have all columns visible to do the a Find, but the kind and depth of the searches will affect how much relational structure you need to be able to reach the data you want. I think you need to define/clarify the parameters of your searches: how many columns, how many days forward or back and what conditions. I'm sure you can do what you want in FM, but the question is developing a relational structure that will support it.
What are you "storing" them in?