6 Replies Latest reply on Feb 14, 2015 1:01 PM by ultranix

    Script to deal with inconsistent number of years



      Script to deal with inconsistent number of years


      I need you to help me with the concept or idea how to get the results I need to get.

      The idea. I have stock market data, which I'm going to import, make some calculations and later use for my testing purposes. I have 30 of Dow Jones Industrial Average stocks. Most of the stocks have 5 years of data (2010, 2011, 2012, 2013 and 2014), but some were listed, let's say in 2012 and it has only 3 years of data. As I'm splitting yearly files into google-2010, google-2011, google-2012, etc. After making calculations in each yearly file, I want to import all files into one, let's say - google (without any year and dash), because it's faster to make complex calculations with replace field contents scripts with lower amount of data.

      Issues. So the issues here are basically 2.

      1) When I go with a loop script from a table STOCK and set STOCK::tick as a variable and conditionally open every file, how can I open and run calculations with correct number of files, i.e. if there's 5 years of data, open 5 files, if 4 - 4, and so on. (and the go to next record and repeat the process)?

      2) When I have all files with calculation scripts performed, how can I import correct number of files - if 5 files with calculations, then import all 5, if 4 - 4 and so on. ?

        • 1. Re: Script to deal with inconsistent number of years

          Are these really separate FileMaker Files or a set of tables all in one file, but one table for each year?

          Does each table/file store data for a single stock for that year or all 30 stocks?

          Can you explain the starting context for 1) in more detail.

          • 2. Re: Script to deal with inconsistent number of years

            Yes. separate filemaker files. It's the only solution I can think of to speed up the process, because the more records are in the file, the slower (and exponentially) the process of Replace field contents is. As you might have been familiar with my complex 4-6 level calculations, where one record's result is dependent from another and several levels down, I decided to split files into separate years, or 200-220 records for each split.

            Each file (one table in the whole file) stores data for a single stock. That BASE file is the "controller" file, in which I just import summarized data from all the stock files. Scheme should look like this: Analytics-BASE.fmp12 controls Analytics-GOOG.fmp12 (google stock), Analytics-AAPL.fmp12 (apple stock), Analytics-MSFT.fmp12 (Microsoft stock), etc. files, runs scripts in them and imports summarized data

            1) Context. OK.

            First of all I open Analytics-BASE.fmp12. It then goes to STOCK layout, where all 30 stocks (their ticker simbols, like AAPL, GOOG, MSFT, etc.) are located. I grab ticker symbol and conditionally open the Analytics-XXXX.fmp12 file (where XXXX is ticker symbol I just grabbed) and then I import .txt file of price data and then run calculations and replace field contents scripts for complex calculations. After that, I import summarized data (summary fields) into Analytics-BASE.fmp12, go to next record and start over again.

            It works all well using static - 1 data .txt file, but I just don't know how to use variable number of data files, as most stocks will have data for all 5 years (and thus I have to have 5 files for each year for the same stock, so it would be Analytics-AAPL-2010.fmp12, Analytics-AAPL-2011.fmp12, etc.) and then I will have to import all those Analytics-AAPL-2010.fmp12, Analytics-AAPL-2011.fmp12, etc. to Analytics-AAPL.fmp12 (where all calculated data for the single stock is stored).

            But it may happen so that the stock is listed in NYSE for 2-3 years, so then I will have only Analytics-EA-2012.fmp12, Analytics-EA-2013.fmp12 and Analytics-EA-2014.fmp12. So here I have to differentiate and only perform scripts for those files. And when it comes to import Analytics-EA.fmp12 (where all calculated data for the single stock is stored), it should contain of only those 3 files -2012.fmp12, -2013.fmp12, -2014.fmp12.

            Is it getting any clearer?

            • 3. Re: Script to deal with inconsistent number of years

              because the more records are in the file, the slower (and exponentially) the process of Replace field contents is.

              I would expect that to be true for the number of records in a table, not in the file as a whole. I see no reason not to use multiple tables in the same file as this simplifies the issue you are dealing with here. The key culprit, as I understand it,  for your delay is that FileMaker bogs down when your replace field contents operation starts generating numerous updates to an index on that field. Those indexes, however are specific to a given table, not the file as a whole. I thus would not expect to a significant difference in how long it takes to do this using tables all in one file instead of a group of files with one table per file.

              However, I'm speculating from my understanding of how this works here, your actual tests, if you compared using several tables in one file vs several files with one table per file and got different results, then I stand corrected.

              I grab ticker symbol and conditionally open the Analytics-XXXX.fmp12 file

              That's the part that has me scratching my head. What do you mean by "conditionally open" and what method do you use to open a file not specifically named in an external data source reference in the file. This is where moving from table to table inside a single file becomes a much simpler proposition.

              The only way that I can think to do what you want starts with a plug in or system script to lists the contents of the folder, then uses Send Event or Open URL to open each file and each file would have an "onFirstWindowOpen" trigger that performs the necessary import and analyze script.

              But even there, I'm not sure that everything can be made an automatic batch operation.

              • 4. Re: Script to deal with inconsistent number of years

                As for different tables vs. different files. I do a lot of testing and updating fields and calculations. If you suggest me to have 5 carbon copies of 1 table, i.e. Year 1, Year 2, Year 3 and so on, I don't see that as a solution, as I update calculations and field setups via testing. So if I update one but accidently forget to update other 4 tables, I will get major errors and I don't want that. It may sound ridiculous, but I have 1298 fields (and counting) in my table, so I don't want to add another tables and mix where I update fields and where not.

                Automatic batch operation is made and running. Yes, I use "OnFirstWindowOpen" trigger and Send Event with variable.

                Back to the subject, what I was thinking on those "conditional years". I just direct the script to the folder, where it should find the .txt file, and if it's not there, skip it. If it's there - import data and make a copy of current file as Analytics-XXXX-YEAR.fmp12. Then go to the next year. Erase all the data from Analytics-XXXX.fmp12 and go check for another year's data. It should go from the most recent year - 2014, then check for 2013, 2012, 2011 and finally to 2010.

                So, let's say, first it checks for 2014, finds the .txt file and runs all the process, then checks for 2013 and finds .txt and runs all the stuff again. Then - it checks for the 2012 data and doesn't find anything. So (as we go backwards and now the last valid year was 2013- so the Analytics-XXXX.fmp12 contains only of year 2013 data) there it should import only Analytics-XXXX-2014.fmp12 file and we're done.

                I want to make sure you understand it right. When you perform check for year 2014 and you have data for that year, Analytics-XXXX.fmp12 contain of that year data and another copy of the file is saved with different name - Analytics-XXXX-2014.fmp12. Once it's done, I check for data of year 2013 and if I have data for that year, I delete all records and import year 2013 data to Analytics-XXXX.fmp12 and also save another copy of the file with different name - Analytics-XXXX-2013.fmp12. Once this is done, I check for data of year 2012 and if data is found, I delete all records in Analytics-XXXX.fmp12 and start over again importing year 2012 data. So, basically, I import into Analytics-XXXX.fmp12 file, but, as I got the last valid year of data, I only import sequential years - so in this example, data of 2012 is already in Analytics-XXXX.fmp12, so I only add Analytics-XXXX-2013.fmp12 and Analytics-XXXX-2014.fmp12.

                The question here is - how to transform my thoughts into actual scripts and reality?

                • 5. Re: Script to deal with inconsistent number of years

                  Ok but FileMaker can't just "check for a file" except under very limited circumstances such as a folder located inside the documents folder. You'd need a plug in or system script that can list the files inside a given folder in order to "check for that file". Once you have that missing piece in place, you can do what you want here as far as I currently understand it.

                  PS. FileMaker can check the contents of folders inside the system's Documents folder by using the get ( DocumentsPathListing ) function and parsing the resulting list of entries down to those for a specific folder located inside Documents.

                  • 6. Re: Script to deal with inconsistent number of years

                    I have a simpler concept. After you try to import and no file is found, you set this: If (GetLastError = X) (where X is last error number for "File is missing" or smth), the do this and that. So if we trigger this error - then stop remaining script and go to import actions.