Help needed with scripting, db setup

[Jtrader]

I'm trying to setup a DB using FMpro to do a search based on a date range; it could be from 1 day to many days. I have 3 columns of basic data. A date column, price column, and volume column. I'm trying to collate each price level and sum all of the volume associated with that level w/in my search range. A quick example of what I'm trying to do:

9/10/04 1050 590

9/10/04 1051 100

9/10/04 1052 330

9/10/04 1053 563

9/10/04 1054 455

9/10/04 1055 52

9/10/04 1056 244

9/10/04 1057 50

9/10/04 1058 100

9/9/04 1055 100

9/9/04 1056 20

9/9/04 1057 32

9/9/04 1058 54

9/9/04 1059 666

9/9/04 1060 325

9/9/04 1061 548

9/9/04 1062 52

9/9/04 1063 590

9/8/04 1045 20

9/8/04 1046 300

9/8/04 1047 10

9/8/04 1048 54

9/8/04 1049 63

9/8/04 1050 258

9/8/04 1051 866

9/8/04 1052 989

Date range look up results

9-8 thru 9-10

1045 20

1046 300

1047 10

1048 54

1049 63

1050 258

1051 966

1052 1319

1053 563

1054 455

1055 152

1056 264

1057 82

1058 158

1059 666

1060 325

1061 548

1062 52

1063 590

Can something like this be done with FMpro? Any help much appreciated.

Note - Had a few typo's in my orig post regarding the data example. Sorry

[LiveOak]

How about describing what you are trying to do without getting into the detail. I'm trying to build a database to...

Next, what are the tables in your file? What does each record represent in each table?

It's easy to forget we're not looking over your shoulder .

-bd

[Lee Smith]

Why not just attach a (striped of confidential information) copy of your files?

[-Queue-]

It sounds like you want a subsummary by price. Note that this will only work for viewing in Preview Mode or printing. You will need to add a subsummary part to your layout by price, put your price field on it, and sort your found set by price. Create a summary field based on the sum of your volume field and add it to the subsummary part. Remove the Body part so that only the subsummary shows. Go into Preview Mode to see the result.

[Jtrader]

The simplest way to explain what I'm trying to create is: I'm keepeing track of personal records of selling varios products. They sell at various prices and have N number of buyers. I'm trying to determine where my products are priced most effectively to draw the greatest number of buyers over a given date range.

It comes down to simple addition of volume, then collate each price level, but when I want to look at monthly or yearly numbers it can take time.

I attached a simple db based on the example above. Hope this helps?

fmproquestiondb.zip

[-Queue-]

See attached modified db with my summary by price suggestion.

fmproquestiondb.zip

[Jtrader]

Queue, that does seem to work based on my orig db. How can I import new records to verify it. Also, would the results your db created be able to be exported and work with any date range (1 day - 360 days)?

Thanks

[-Queue-]

Just add the summary field, subsummary part (on a new layout if you prefer), and a script similar to my Volume Report one in your own file. It won't mess up anything.

When you export records (first making sure they're sorted by Price), add the Price and sumVolume fields to the Field Order. Then click 'Summarize by', check Price, click 'OK', note that there is now an italicized 'sumVolume by Price' in the Field Order. Remove sumVolume from the Field Order, and export.

The date range is irrelevant. The records are sorted by price, regardless of date. So any range or single date will work.

[Jtrader]

Queue, I'm not sure I know what I'm doing. I searched for my date range then sort by price, then I ran the volume report script. How does the summary report come into effect? Is this something that I need to create? How would I say to summarize the volume for all like prices?

ie:

1020 vol 20

1020 vol 60

to equal

1020 vol 80

Thanks

[-Queue-]

Go into Layout Mode and use the layout menu (rolodex) on the Status Area to scroll to the Summary Report layout and see how it's set up.

The script sorts by price, so you don't need to manually do it again.

[Jtrader]

It still seems like I'm over looking something. When I run the script the resulting page comes up blank. Also, I'm not sure the summary field is defined to do what I'm trying to do....or even if can be defined to do what I want. You had it set to sum total volume, where I want just the total volume for each price level and to just show that price level 1 time with all volume associated with it.

Thanks, any guidance much appreciated.

[-Queue-]

A summary field totals itself depending upon the layout part it's in. If it's in a subsummary by price and the records are sorted by price, then the total volume is categorized by price. This is what you are seeking.

Are you referring to the script in your file or my file? If it's in your file, make sure the script is sorting by price or else the subsummary by price won't be visible, and you will have a blank layout.

[Jtrader]

I'm refering to your file. Originally when I said it worked correctly I spoke to soon, b/c I didn't understand what I was looking at. I'm not saying it doesn't work, I just can't get it to work. Is there something I need to do to sort by price for the results to be visible? I thought you mentioned that the sort by price was needed after I had the date range b/c the script did the sorting. Please excuse my greenness.

Before I incorp your ideas into my db I wanted to get your file to work.

Thanks again.

[-Queue-]

The script sorts by price, so all you should need to do is select it from the Scripts menu and it will create a report for the found set. If there are no records found or you've managed to sort the records differently and 'Replace' the sort order in the script, then you will get a blank report. Otherwise, you should see the report as you originally did.

To see if the sort order has changed, run the report, then go to Records -> Sort and see if it is still sorting by Price ascending.

[Jtrader]

I downloaded your file again, it works. I must have done something to it while trying to figure out how to use it. I'll upload the file I messed up if your interested in taking a look.

One thing I'm still not clear on is once I have the report I want, how can I export the results as text for excel?

Thanks, you've been more then helpful.

fmproquestiondb2.zip

[-Queue-]

Ah, the problem was you changed the summary report layout to view as a table instead of a list. Subsummaries don't work with tables, so the report appeared to be blank.

Okay, I'll elaborate on the export process. First, after you've run the report and are back on the main layout in Browse Mode, go to File -> Export Records and choose a place to save your exported file and name it. You can save it as tab, mer, or csv. If you save it as mer (merge file format) but name it with a csv extension, it will keep the field names as the first row when you open it in Excel, and Excel will be able to open it automatically (due to the csv extension) instead of having to import it as it does with mers.

Once you've named it and clicked 'Save' (or pressed Enter), you will see the 'Specify Field Order for Export' dialog. Double-click on the Price and sumVolume fields to move them to the 'Field Order' list. Then, click the 'Summarize by' button and click on Price, so that it puts a check next to it. (The reason Price is the only field available in the list is because it's the only field by which the data is currently sorted, and therefore, the only field which can be used to summarize the data.) Click 'OK'. Now you'll see sumVolume by Price in the 'Field Order' list. Double-click on the original sumVolume field to remove it from the list, so that you only have Price and sumVolume by Price. Now, click 'Export'. When you open the saved file, you'll see one row for each Price with the total of its Volume. (This is due to the summarizing by Price. If you hadn't done that, you would see one row for each record in the file with the total volume for all records in the file.) Now, open the Volume Report script and add an Export Records [No dialog, Restore] step at the end of it. This will save all the export settings and not allow anyone to change it. If you ever do want to change it, perform another export manually, then open and close the script and select 'Replace' for 'Export Order' in the dialog.

[Jtrader]

Thanks Queue, I'm beginning to understand it a little better looking thru your example and reading your text here. I've uploaded a time factor to the db, made a script, and a summary report to sum volume based on time of day for each order. I'd like to see vol of orders in half hr increments ideally, but I'm not sure how to specify half hr blocks. My report shows the total volume for the entire searched range at every time interval. Can this be broken down any further?

Thanks for getting me on the right path

fmproquestiondbtimeadded.zip

[-Queue-]

How do you want to group the half hour increments? Should 10:05 appear in the 10:00 or 10:30 group?

[Jtrader]

Yes, everything between 10:00 thru 10:30 should be included if I'm understanding you correctly; (ie, 9-9:30, 9:30-10, etc)

TIA

[-Queue-]

Create a calculation of Time( Hour(Time), Case( Minute(Time) < 30, "00", "30" ), 0 ), with a Time result.

Add a subsummary by Date part, and change the subsummary by Price to be by the new calculation field. Change the Price field on the report to the new calc. Sort the report by Date, then the new calc.

[Jtrader]

Thanks for your reply. I haven't had a chance to play with your suggestion but will give it a go over the next few days.

[Jtrader]

Hi Queue, I tried the formula you provided, it works well. I decided I need to calculate volume every 5 minutes, I tried stringing many >0<=05, "05", but because the statement is a basic "if/then" there were too many variables.

I also thought I could have a series of statements like this:

Time( Hour(Time), Case( Minute(Time) > 05 and <= 10, "10"), 0 )

Time( Hour(Time), Case( Minute(Time) > 10 and <= 15, "15"), 0 )

but this doesn't seem to work either. Is there a way this can be done?

TIA

[-Queue-]

It would be more like

Time( Hour(Time), Case( Minute(Time) > 0 and Minute(Time) < 6, 5, Minute(Time) < 11, 10, Minute(Time) < 16, 15, ... ), 0 ).

However, I would use something like either

Time( Hour(Time), (Int((Minute(Time) - 1) / 5) + (Minute(Time) > 0)) * 5, 0 )

or

Time( Hour(Time), Minute(Time) + (5 - Mod( Minute(Time), 5 )) - 5 * (not Mod( Minute(Time), 5 )), 0 ).

[Jtrader]

Add a subsummary by Date part, and change the subsummary by Price to be by the new calculation field. Change the Price field on the report to the new calc. Sort the report by Date, then the new calc.

Add a subsummary by Date? Shouldn't it still be by sum-volume then the new time calc will replace price? I've played with it a few ways, the results have been total volume for the whole day listed at each 5 min interval, the days date with all the time intervals listed, and a few others that I don't exactly know what to call them. I'll try to upload a db a little later.

TY

[-Queue-]

There was no sum-volume subsummary, nor should there be. You want the subsummary based on the break field(s), not the field to be summarized. The subsummary by Date ensures that you don't group, say 10:00, records that have different dates. If you're already limiting your find by a specific date, then it wouldn't be necessary. In that case, you would only need a subsummary part based on the new calc.