We help IT Professionals succeed at work.

Which database can do this search?

505 Views
Last Modified: 2012-05-11
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.

Comment
Watch Question

awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
>>I'm currently using FileMaker to store historical stock market index quotes<<
What are you "storing" them in?

Author

Commented:
I'm not sure what you are asking.  FM is a database package similar to Access.  They are stored there.
Will LovingPresident, Dedication Technologies, Inc.
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
@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?
Will LovingPresident, Dedication Technologies, Inc.
CERTIFIED EXPERT

Commented:
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" meaning HistoricalIndex - Self-Join - Previous 1 day.

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.

Author

Commented:
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?
Will LovingPresident, Dedication Technologies, Inc.
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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.  
Will LovingPresident, Dedication Technologies, Inc.
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
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

Author

Commented:
Also, yes - it is a single Find request and Range is a number.
Will LovingPresident, Dedication Technologies, Inc.
CERTIFIED EXPERT

Commented:
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

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()" )

Author

Commented:
I use the wildcard and it works fine.  But that isn't the issue.
President, Dedication Technologies, Inc.
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?
Will LovingPresident, Dedication Technologies, Inc.
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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.
Will LovingPresident, Dedication Technologies, Inc.
CERTIFIED EXPERT

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.