|
Please e-mail us at commoditydata-support@morningstar.com if you have comments or suggestions.
INDEX (Also see the Table of Contents)
Commodity DataServer and API Related Questions
Commodity Query Language Questions
Commodity Charts Application Questions
Commodity DataServer Excel Add-in Questions
Commodity DataServer Database Questions
Data Questions
General
Where are the API docs located?
The API documentation is located in "Part III: Morningstar Development" in the Commodity DataServer Data and Development Guide.
What API's are available for use?
Open architecture provides interfaces to the Market Information Machine using C/C++, Java, .NET (.NET flavors include C#, J# and VB.NET) and the BMIM scripting language. Please consult "Part III: Morningstar Development" in the Commodity DataServer Data and Development Guide for more in-depth information into the use of Morningstar Application Programming Interfaces.
How do you run daily updates by hand?
Telnet to the Morningstar server. Login as the user lim.
Type /home/lim/cron_updates.sh
How do I load my own data into the Commodity DataServer?
See the "Best Practices for Data" chapter in the Commodity DataServer Data and Development Guide.
How do I properly start up (shut down) a Commodity DataServer?
For instructions on starting up or shutting down a Commodity DataServer, see the appropriate guide:
Tell me about the `.bak' file
When a process locks the database, it creates a backup of the schema file. The backup file uses the extension `.bak'. This file is almost, but not quite, the same size as the original schema file --- it has an extra four bytes, used for locking purposes.
When the `.bak' is created, the creating process also acquires an operating system lock on the file (using `fcntl'). This lock is automatically released by the operating system when the process exits, regardless of the cause of exit. In particular, if the process crashes, the lock is released.
Hence, if a `.bak' exists, a Commodity DataServer process can determine whether the schema file is currently being updated by a process, or whether the `.bak' is left over from an updating process which crashed. In the first case, some process is updating the schema, and since schema changes are not visible until the schema is unlocked, the Commodity DataServer process can only proceed at its own risk. In the second case, the Commodity DataServer may choose to recover the schema file from the `.bak' file.
The issue is more complicated in the client/server world. It is the Commodity DataServer, specifically the `xmim_slave_server', that knows enough about the Commodity DataServer `.sch' and `.bak' files to permit a recovery. Starting with version `3.1.3', the Commodity DataServer uses a dedicated slave process to handle updates. Updating clients talk to the `xmim_server' to perform a `lock_files' command, which has the effects of creating a new update server if one has not been already created, and then letting the client know about the dedicated update server. From that point on, the updating client will talk directly to the update server.
Now, suppose a `.bak' file exists, before `xmim_server' is started up. When it comes up, the `xmim_server' will create a slave to process the `.sch' file and create a memory mapped version of the schema, using the extension `.mmap'. This slave will detect the presence of a `.bak' file. If the `.bak' is currently locked, some other Commodity Query process is performing an update, and so the slave ignores the `.bak' file completely. Otherwise, the slave will attempt to recover the file before proceeding. Note that the recovery is only possible is the slave has write permission to the `.sch' file. After the recovery attempt, the slave processes the `.sch' file and creates a new `.mmap' file. Subsequent slaves will not see either of the `.sch' or `.bak' files; they will work exclusively with the `.mmap' file instead.
If the `.bak' file is created after `xmim_server' is brought up, it will be noticed only when a `lock_files' command is executed. When this happens, the master will determine if it already has a dedicated update slave server or not, and if the dedicated update server is currently alive. If the dedicated update slave is not already up and running, the server will create one. This slave will attempt to read the `.sch' file, notice that there is a `.bak' file, and initiate a recovery if the `.bak' file is unlocked. Note that the `.bak' may have been created by the previous dedicated update server. This is certainly the case if the master has a dedicated update server that is no longer alive.
From an updating client's perspective, including `bmim_client', the process is as follows. The client connects to a Commodity DataServer and then locks the database. At this point, the server processes the `.bak' file, if recovery is needed. Subsequent requests from the client go directly to this dedicated slave. Now, suppose the client makes a request that crashes the dedicated slave. At this point, the client receives an `XMIM_FATAL_ERROR' code. Well-behaved clients, such as `bmim_client' will exit when they receive this error code. Since the updating process died, the `.bak' is left in the system in an unlocked state. This `.bak' file will trigger a recovery the next time any of the following happens:
-
A new `xmim_server' is started, since this will trigger the creation of a new `.mmap' file, as described above.
-
A new `bmim_stand_alone' or "one_proc" is started, since it will begin by reading the `.sch' file and notice the `.bak' file.
-
A `lock_files' command is sent to the old `xmim_server', since this will make the server realize the dedicated update slave is dead and force the creation of a new slave. Note that this `lock_files' command can be sent to the server using `bmim_client' or with any API client that performs `XmimVaLockDatabase'.
In particular, notice that if `bmim_client' is used to run two update scripts, `upd1' and `upd2', and `upd1' somehow crashes the dedicated slave server, simply running `bmim_client' on `upd2' will force a recovery -- but the `upd1' updates will be lost. This is precisely what happens in the non-client/server world with `bmim_stand_alone' instead of `bmim_client'.
Why does Commodity Query message state "Could not open temporary file"?
This can happen if the directory that is pointed to by the Java system propoperty “java.io.tmpdir” is not writeable, possibly because it's full. Another possibility is that the directory is not full, but the file that Commodity Query is trying to write is simply too large.
There is no direct workaround for this. If the problem is that the directory is full, a possibility is to delete some files from it. Commodity Query itself has been known to leave files in this directory, and these can be deleted. See the section My temporary directory is full of Commodity Query files below for more details.
My temporary directory is full of Commodity Query files
Commodity Query uses the directory that is pointed to by the Java system property “java.io.tmpdir” to store many different temporary files. Note that each instance of a running Commodity Query process will be creating temporary files here, so it's possible to end up with a large number of Commodity Query files in this directory.
There are several types of temporary files in this directory. There may be others, but we know of the following. The first type of file looks like xmim_startup.N. This file is created by the "one_proc" version of the system, and it's used to fix the old "UDP error," which happened when two instances of "one_proc" registered the same port to talk to the xmim_browser. This file is deleted when the "one_proc" process terminates. It cannot be deleted sooner, since it serves as a lock file, locking out a given port number. Should this file be (manually) deleted while the process is running, there will probably not be an immediate error, but there will likely be a problem when the next "one_proc" is started up.
Another type of temporary file has the form xmim_reportXXXXXX. These are created the first time a query is executed. What happens is that reports are written to this file, and then the file is displayed in the results window. The reason the file is not deleted at this point is strictly historical. It would take a lot of modifications to the code to delete this file as soon as the results window is displayed; in particular, the file is used for printing results. Consequently, the file can stay around for some time. This file may be deleted manually; however, this may generate some errors, particularly with printing the results.
Yet another temporary file looks like xmim_queryXXXXXX. These files are created while Commodity Query is trying to print a query. They are deleted immediately after the print command. They are perfectly safe to delete manually -- at worst, a given print command will fail.
Another temporary file has the name xmim_data_file_XXXXXX. These files are use to support the LET...DATA SOURCE Commodity Query command. They can be deleted manually.
As a general rule, Commodity Query deletes these temporary files on termination. Unfortunately, Commodity Query may crash, or it may get killed with an uncatchable signal (for example, using the `zap' program). In these cases, it is unable to delete the temporary file, and thus the temporary directory becomes cluttered. There is no workaround for this. When it occurs, the user must delete the Commodity Query files manually from the temporary directory, ensuring, of course, that he is not deleting a file from a current Commodity Query process.
How would one extract data from the Commodity DataServer?
There are several methods to extract data from the Commodity DataServer. Morningstar provides three GUI applications to query the database: Commodity Query, Commodity Charts and the Commodity DataServer Excel Add-In. Additionally, you can use any of our API’s: C/C++, Java, .NET (.NET flavors include C#, J# and VB.NET) as well as the BMIM scripting language. Also, you can use the Commodity DataServer publisher, a tool in which we push out the periodic updates to other systems. Lastly, we integrate with S-PLUS, MATLAB, and many other applications.
For more information, see the "Best Practices for Extracting/Reading Data" section in the "Best Practices for Data" chapter of the Commodity DataServer Data and Development Guide.
Is there high availability support for Commodity DataServers?
Select this link to view the document High Availability Support for Commodity DataServers.
Do you have Microsoft.NET compatibility/compliance?
Yes, the .NET API will work with any .NET language. It has the same functionality as our Java API. The download for the .NET API is available from my.morningstarcommodity.com (If you do not have a my.morningstarcommodity.com account, please contact Morningstar Client Support at: commoditydata-support@morningstar.com.) See the "Commodity DataServer .NET API Installation Instructions" chapter in the Commodity DataServer Data and Development Guide for instructions on how to install and setup the .NET API.
Back to Top
What is LET?
LET can be used to assign variables to a list of symbols or a custom calculation. For more information see the "Let Statement" chapter in the Commodity Query User Guide.
Why do I get fewer LET iterations than expected?
Consider the following query:
LET
thePattern = IBM, US
theAnalog = IBM, TY
ATTR corPeriod = 50
ATTR corMinimum = .95Execution of this LET statement will result in only a single iteration where IBMwill be used for both the variables thePatternand theAnalog. There will not be a second iteration so USand TYwill not be used. When multiple expressions appear within a single LET, execution will result in sequencing through the list of assignments for all variables simultaneously such that, at each iteration, the next assignment is made for each variable. The iteration stops when any of the variables runs out of assignments. Therefore the number of iterations to be executed will be the length of the shortest assignment list.
In order to have the system execute two iterations such that USand TYwill be used, the above query could be written as:
LET
thePattern = IBM, US
theAnalog = IBM, TY
ATTR corPeriod = 50, 50
ATTR corMinimum = .95, .95The assignment values for the two attribute variables had to be replicated so they would be used for a second iteration. Alternatively, two LET statements could be used to accomplish this:
LET
thePattern = IBM, US
theAnalog = IBM, TY
LET
ATTR corPeriod = 50
ATTR corMinimum = .95In using two LET statements, no replication of values is necessary for the attribute variables since, semantically, multiple LETs are nested, resulting in a cross-product of assignments.
To further emphasize the concept of using multiple LETs versus a single LET with multiple expressions, consider the following:
LET
theSec1 = IBM, US, SP
theSec2 = IBM, TY, DJIAThis LET will result in 3 iterations, the substitution list being: (IBM,
IBM), (US, TY), (SP,DJIA). However, the following:
LET
theSec1 = IBM, US, SP
LET
theSec2 = IBM, TY, DJIAwill result in 9 iterations, the substitution list being: (IBM,
IBM), (IBM, TY), (IBM, DJIA), (US, IBM), (US, TY), (US, DJIA), (SP,
IBM), (SP, TY), (SP,DJIA).
What is the difference between using the syntax "15 day" and "15 value"?
Day specifies a calendar day and can reference a holiday or other non-trading day if it falls during a normal Monday through Friday. If you say "1 day move of MSFT" on July 4th (holiday), you will receive a 0 because the price did not change over the course of the previous day. If you say "1 value move of MSFT" on July 4th, you will receive a NaN because their is no value for the symbol MSFT for that day. See Why do I get NaNs even though I'm using value mode? below for more information.
Why do I get NaNs even though I'm using value mode?
(Note: this is not an issue when using Commodity Query version 4.3.01 and later.)
Consider a typical query in value mode:
SHOW
1: FOO
2: 10 value average of FOOThe answers will give a NaN value for some entries in the second column. This is because of one of several reasons.
First of all, whenever `FOO' is a NaN, so will any "value" computation made on it. This is done to avoid double counting -- i.e., the alternative would be to replicate either the previous answer or the next, and that will lead to statistical over counting whenever a study is based on another one (as with an average of the 1-day moves to estimate volatility.)
Secondly, at the very beginning of history, the system will be unable to find 10 values of `FOO' to average. In these cases, the Commodity DataServer will return NaN for any "10 value" computation. The alternative would be to change the weight of the first few values -- i.e., at the beginning of time, the "10 value" average would only be looking at 9 values, or 8 values, etc. It is precisely to avoid these irregularities that value mode is preferred (by some) over daily mode, hence it would be counterproductive to change this semantics.
Last of all, value mode works by "skipping" NaN values in the source time-series, in this case `FOO'. Unfortunately, if there are more than three consecutive NaNs, the Commodity DataServer will treat this case as if it was the beginning of a new history. That is, in a "10 value average," it will result in a string of at least 10 consecutive NaNs, since it will take at least 10 days to generate enough values for the average to get restarted. See Why do I get long sequences of NaNs in value mode? below for more details.
Why does value mode not work for date conditions?
Many people have tried to mix value mode with date conditions. For example, take the query:
SHOW
1: DJIA
WHEN
Date is 1 value after yearly Close What people are trying to do with this is to get the first value of `DJIA' after the close of the year. I.e., the yearly close happens on 12/31; a day later is 1/1 which is a New Year's Day, a holiday. So, people expect the query to return the value of `DJIA' on the day immediately after New Year's Day.
Unfortunately, that is not how the system works. The problem is that value mode works by skipping NaNs from an underlying time-series. In particular, it does not know about holidays, only NaNs. The date condition does not have an underlying time-series, and so there are no NaN values to skip. What this means is that the date condition does not know which days to skip after the yearly close, and so it doesn't skip any of them. The result of the query, therefore, is the value of `DJIA' on the day (not value) after the yearly close --- i.e., on New Year's Day.
The Starting Equity Option
By default, the P&L system operates on what I like to call the "rich uncle" mode. In this mode, the system starts out with zero equity. When the system wants to enter a trade, it borrows money from the "rich uncle." This money is returned (without any interest, since the loan is within the family, after all) as soon as the system starts making some profit.
Of course, in this model, the computation of percentage return and all P&L statistics that depend on percentage return (e.g., Sharpe ratio) are skewed. After all, you start with 0 equity, you buy IBMat 100, sell it at 105. At this point, you give your uncle his 100 dollars back, and you now have an equity of 5. So, what is the percentage return from 0 to 5? Infinite, right? Clearly, this is not what is meant. What the Commodity DataServer will do in this case is to assume that the money borrowed is accounted for at the beginning of the period as well as at the end. That is, rather than looking for the percentage return from 0 to 5, the Commodity DataServer looks for the return from 100 to 105, i.e., 5%. (Incidentally, the "loan" is visible through the closed equity, which will show a value of -100 while the loan is in effect. In particular, on the day of the trade, the closed equity will be -100, the open equity will be 100, and so the total equity is still 0.)
This simple scheme works reasonably well, but it does have its idiosyncrasies, especially if the system has to borrow money in multiple occasions.
The starting_equityoption is designed to get around this problem. It allows the user to specify his initial starting equity; i.e., the amount of money he has in the bank. When the system makes a trade, it will use the money from the bank, instead of trying to borrow money from a rich uncle. This resolves the artificial problems with percentage return. After all, you start with, say, $100,000. Then, you make some trades and your total equity is $125,000. Nobody can question that the return is 25%. Note, however, that if the system runs out of cash, it will stop trading, since there is no "rich uncle" or "fairy godbanker" to give it more cash.
Note, the use of starting_equitydoes not affect the total amount of shares that are actually bought. I.e., if your signal says
BUY 1 share of IBMCommodity Query will buy a single share of IBM, whether you have $1,000 starting equity or $1,000,000 starting equity. In fact, "starting" has nothing to do with it. If you want the number of shares to vary with your current equity, use the dollar amount or equity percentage when you specify the trade. For example, to be fully invested, you would write the following:
BUY 100 % of IBM
Why Doesn't the P&L Trade on Days with NaNs?
Suppose you wanted to test this simple scenario: BUY the DJIA at the beginning of January, and sell at the end of January. The following should do it:
1: ORDER
1.1: BUY 1 contract of DJIA
WHEN
Date is first day of January
EXIT
Date is last day of JanuaryIf you try that, you will probably be disappointed. Many (if not all) years will not enter a trade.
Why? It is because the first day of the year is a holiday. The system gets a signal to trade on 1/1/98 (for example), but when it tries to enter this trade, it can not find a price -- it sees a NaN instead. Since there is no price, Commodity Query can't enter a trade.
But, what about using the previous price, or the next price? Some time in the past, it was deemed incorrect to use the previous price. The rationale was that since the signal occurred today, there was no way to squeeze in a trade at yesterday's price. Consider, for example, a sell signal based on some catastrophic event that just happens to fall over a long weekend. The following day seems more reasonable. However, the consensus was not to do this by default. The user can explicitly request that Commodity Query continue trying to enter the trade for a period of time (including "forever"), but by default Commodity Query will only attempt to trade on the same day that the signal is found.
How does the user explicitly say he wants to keep the signal active for a few days? By using the ORDER ACTIVEclause in the trade. The query above should be stated as follows:
1: ORDER
1.1: BUY 1 contract of DJIA
ORDER ACTIVE 1 week
WHEN
Date is first day of January
EXIT
Date is last day of JanuaryBecause the ORDER ACTIVEis specified, Commodity Query will try to fill the order for a period of a week -- i.e., the first week of January. Naturally, you can set the period to a month, year, or some other arbitrary time period. And if you wanted to leave the trade open until it was filled, you would use the GOOD_TILL_CANCELLEDor GTCkeyword as follows:
1: ORDER
1.1: BUY 1 contract of DJIA
GTC
WHEN
Date is first day of January
EXIT
Date is last day of JanuaryOne quick note concerns tick mode. Notice I said earlier that, by default, Commodity Query keeps the trade order active only during the day it is placed. The wording was deliberate. If you place a trade at 8:27am but there is no data at that time, Commodity Query will try to fill the trade at 8:28am, then 8:29am, and so on until the end of the day.
Now that you understand how Commodity Query behaves when it sees a NaN when entering a trade, you would expect similar behavior when Commodity Query attempts to exit a trade. However, Commodity Query does not keep discard exit signals on NaNs in the same way it discards entry signals. Instead, it behaves as if the GTCoption was specified with all exits. I.e., once it gets an exit signal, it will continue to exit the trade until it is successful. This behavior may strike you as odd or inconsistent, and it probably is. As with most of P&L, we arrived at the behavior by soliciting input from people inside our company, as well as from our customers. Unfortunately, the consensus process does not always result in the right answer, only the most popular one.
Why Doesn't Setting a Date Variable in a DO Block Work?
When people ask this question, they're often frustrated because a query similar to the following doesn't do what they want:
DO
dateVar = current date
WHEN
Date is 3/1/98
SHOW
1: IBM on previous date:dateVarThe frustration is understandable. They suspect that the DOblock sets the value of dateVar to be 3/1/98, and so when the SHOWblock is executed dateVar is the same as 3/1/98, and the query should show the value of IBMon that date.
At the root of this belief is the assumption that the system executes each of the query blocks in order. I.e., the first query block is executed, then the second query block is executed, and so on. The reality is that query blocks are executed in parallel. The system chooses which dates are interesting by looking at the query. In this example, it would decide that all days that have IBMdata are interesting. Then, on every day of this range, the system will execute the query blocks in sequence. That is, assume that we have IBMdata from 1/1/70 to 12/31/98. The system will start on 1/1/70, execute the first query block on that day, then execute the second query block on that day. Next, the system moves a day forward to 1/2/70. Again, it executes the first block, this time on 1/2/70, then it will execute the second query block, again on 1/2/70. Since the DOblock assigns the variable dateVar only when the WHENcondition is satisfied, i.e., when the date is 3/1/98, on all days previous to this dateVar will retain its initial value, namely zero. On all those days, i.e., before 3/1/98, the SHOWblock is using the value zero for dateVar, and so the SHOWis not displaying the answer the user was expecting.
At this point, the exasperated user would be justified in saying "Fine, but why does the system behave in this absurd manner?" In fact, what the system is doing is not absurd, and a little bit of thought will bear this out. Consider the following query fragment, typical of many such examples:
1: ORDER
1.1: ...
WHEN
state == 0
AND
...
AND
state = 1
2: ORDER
2.1: ...
WHEN
state == 1
AND
...
AND
state = 0In this example, what the user has in mind is for the system to alternate trading between the first and second block. The state variable is set to either 0or 1to indicate which block is currently "active," and only the active block may place a trade. Clearly, the user does not intend for the first block to execute to completion, and then for the second block to execute. For that manner, the user does not expect the system to place all the trades from the first block first, and only then to consider the second block.
However, there is some good news. Instead of using the DOblock above to set a date variable, which in any case does not work, it is likely that a simple assignment will do the trick:
INITIALLY
dateVar = construct_date (1, 3, 98)
SHOW
1: IBM on previous date:dateVarNote, however, that it is more efficient to use the constant 3/1/98instead of the variable date:dateVar wherever possible.
Why do I get incorrect dates when I use weekly mode?
When this question is asked it is generally posed in the context of the system returning answers in the wrong month. For example, take the query:
SHOW
1: percent_move from today to 1 week later of US
WHEN
Date is AprilWhen this query is run with the execution units set to 1 week, there will be several occurrences dated early May. While this may seem incorrect due to the presence of a condition specifying a date in April, the May occurrences are actually correct and exist as a consequence of using the system in weekly mode. To understand this consider that, in weekly mode, the date condition is true for a given week if it is true for any day in that week and that weekly mode occurrences are always reported on Friday of the week. Often week boundaries do not precisely correspond to month boundaries, thus presenting the possibility that a date condition may be satisfied during the first part of a week but the reporting date (Friday) may fall on the following month. Thus, for the above query, 5/4/79 would be an occurrence reported because Monday of that week was a date in April and, therefore, satisfied the condition.
Explain Execution and Attribute Units
Attribute units are used to specify the frequency of an attribute, for example Close of IBM. The attribute units can be set directly on the attribute, resulting in an attribute such as the
10 minute Close of IBM. It can also be set globally in the "Attr Units" option. The global setting will be used for any attribute that does not set its units directly.
Execution units are used to specify the frequency at which the query conditions are tested. In a simple SHOW-WHENquery, this means how often the condition in the WHENis tested. The execution units are set in the "Exec Units" option.
Confusion often arises when the attribute units and execution units are set to different values. To understand what is happening, consider testing a market condition by hand. You have a charting service, and on a regular basis, you check for a certain pattern.
Suppose you subscribe to a weekly charting service. That is, every Friday you get a new chart, and the chart has one bar per week. Clearly, it is silly to look for your pattern every day, since the chart changes only on Fridays. This is a good rule of thumb: Do not use attribute units that are larger than the execution units ("larger" as in "weekly is larger than daily.")
Now, suppose your charting service is daily, but you only look at the chart once a week, say Friday. In this case, it's possible that the condition you are looking for (say an island reversal) does not occur on the Friday, but it did occur on the Tuesday of the week. Unfortunately, since you only checked on Friday, you missed a trading opportunity. This suggests another rule of thumb: Do not use attribute units that are smaller than the execution units.
Combined, our rules of thumb mean this: Always use the same value for attribute units and execution units. This is sound advice. But, you may ask, why have attribute units at all? It turns out that it is sometimes useful to mix units, but this should only be attempted by advanced Commodity DataServer users.
When would an advanced user wish to mix units? One place where this makes sense is when a low-frequency attribute (e.g., monthly) is used in a condition that also tests daily data. For example, consider this condition:
WHEN
DJIA crosses above monthly High of DJIA 1 month agoWhen this condition is tested in daily mode, each day the value of DJIA is tested against the previous month's high. An important feature of this query is that the monthly attribute is compared against a daily attribute; in effect, the condition itself is daily, even though part of it is monthly. In this way, our first rule of thumb is violated in letter but not in spirit.
Another place where mixing units makes sense can be seen in the following query:
%exec.units: yearly
SHOW
1: The daily Bar of DJIA repeated for the entire year
WHEN
DJIA is downThis query executes in yearly mode. The condition is true in those rare years that the DJIA ends lower than the previous one. Each of those years, the query asks for that year's daily graph of the DJIA. This query violates the second rule of thumb, by using daily attribute units on a yearly query. Again, however, the violation is strictly to the letter of the rule, and not its spirit.
Why does changing units give answers for the future?
Consider the following query executed with the execution units set to weekly:
SHOW
1: DJIA
2: percent_move from today to 1 week later of DJIA
WHEN
Date is 5/_/98When this query is run on 5/13/98, a Wednesday, it gives the following answers:
Date Day 1 2
05/01/1998 Fri 9147.07 -1.005
05/08/1998 Fri 9055.15 1.177
05/15/1998 Fri 9161.77 ??? This looks wrong, because there is a row for 05/15/1998, and it's only 5/13. So, how does the system get data for 5/15? Well, the answer is simple. We are executing in weekly mode. The Commodity DataServer aggregates each week's worth of values into a single number. So, the values for the week 5/4/98-5/8/98 are grouped together into the single value 9055.15. Since the Commodity DataServer uses the ending time of the period for time-stamping purposes, it prints this value with Friday's date. But notice it is really the value for the entire week. Similarly, the Commodity DataServer lumps together all the data for the week 5/11/98-5/15/98 and gives it the 5/15/98 timestamp. As it turns out, there are only two values during that week, namely Monday's and Tuesday's. This is a partial week, but it gets treated like a regular week by the Commodity DataServer. Hence, the value of 9161.77.
Something else looks wrong, however. How about the second column? On 5/8/98, it says the percent move from 5/8 to a week later is 1.177. But, since a week later is 5/15/98, and today is only 5/13, how does it know? The answer is the same as in the previous paragraph. The Commodity DataServer has a value for 5/15/98, because it is using weekly data, and there is data for that week, even if only partial data for the week. This is one place where it would be fruitful to use daily attribute units, so that the Commodity DataServer can see the difference between Tuesday and Friday.
What is the difference between prepending or appending time offsets?
The following example shows the difference between prepending and appending time offsets.
Show
1: IF 1 day ago IBM is more than IBM 1 day ago THEN 1 ENDIF
2: IBM
3: IBM 1 day ago
4: IBM 2 days ago
WHEN
Date is 2001
When using an IF statement, why does the column "Bar" only return the Close, not the Open, High, Low, Close?
The following example query will only return the Close of DELL.
SHOW
1: IF
Date is 7/13/2001
THEN BAR of DELL
ENDIF
WHEN
Date is 7/13/20
When Bar is unused in a value context it reverts to Close. To show the Open, High, Low, Close you will need four IF statements.
Does the Commodity DataServer accept words for numbers?
The Commodity DataServer does not accept words for numbers. For example, "one day later" needs to be entered as "1 day later".
The Commodity DataServer query language is like the PERL software language. If you use a variable it is automatically created with "0" (or NaN as the value). Therefore, "zero days later" actually works for all of the wrong reasons.
Why does a simply study on a futures contract produce incorrect answers?
Consider the following query
SHOW
CL: Close of CL
avg: 3 day average of CL
WHEN
Date is from 2 days before 7/24/2001 to 7/24/2001
Date Day CL avg
07/20/2001 Fri 25.59 25.15
07/23/2001 Mon 26.12 25.70
07/24/2001 Tue 26.31 26.12 **
Avg 26.01 ** 25.66
Based on the closing prices, the 3 day average on 7/24/2001 should be 26.01, but “avg” on 7/24/2001 shows 26.12.
The discrepancy in the results is based on Commodity Query’s automatic adjustment of the price gap that occurs between contracts on the rollover day. When studies are performed on a continuous-series futures contract, such as the default futures symbol, and the period of time covered in the calculation spans a rollover between an expiring contract and the next contract, then the price gap that occurs between contracts on the rollover day is arithmetically removed from the series so that the price discontinuity doesn't interfere with the results. In order to use the non-adjusted prices, use the key word “front”.
Going back to our example:
SHOW
CL: Close of CL
avg: 3 day average of CL
frontavg: 3 day average of front CL
Date Day CL avg frontavg
07/20/2001 Fri 25.59 25.15 25.15
07/23/2001 Mon 26.12 25.70 25.47
07/24/2001 Tue 26.31 26.12 26.01
The average of the non-adjusted (front) CL prices on 7/24/2001 now reflects the 3 day average of the closing CL prices.
How do you calculate a 10 day moving average using only the "2nd nearby contract" data only? In other words, upon rollover always pick up the data the 2nd nearby contract data would have been using, not a combination of some 1st and some 2nd.
You need to write an IF THEN statement which considers the proper numbers each day to make the function work. See the example below. Note: the special variable is "special.10day".
Spreadsheet detailing the results of the query.
How do you write an Commodity Query query that retrieves hourly data but runs with daily execution units?
Use a "repeated" statement inside your SHOW and use hourly attribute units. See the examples below.
Example 1:
Example 2:
How do you produce an Commodity Query chart with the MEAN and 2 STD's above/below as constant lines?
%graph.back.color: "black"
%graph.linetype: "MEAN" double_dash
%graph.linewidth: "MEAN" 3
%graph.plot.color: "MEAN" "red"
%graph.axis.color: "MEAN" "red"
%graph.scalemin: "MEAN" -20
%graph.scalemax: "MEAN" 25
%graph.viewport: "MEAN" "0 0 100 100"
%graph.linewidth: "2STDabove" 3
%graph.plot.color: "2STDabove" "blue"
%graph.axis.color: "2STDabove" "blue"
%graph.scalemin: "2STDabove" -20
%graph.scalemax: "2STDabove" 25
%graph.linewidth: "2STDbelow" 3
%graph.plot.color: "2STDbelow" "green"
%graph.axis.color: "2STDbelow" "green"
%graph.scalemin: "2STDbelow" -20
%graph.scalemax: "2STDbelow" 25
LET ATTR myMean =
average from 59 days before SPX last_data_day to SPX last_data_day
of 1 value % move of Close of SPX
# this says grab the mean of the daily % moves for the last 60 days
LET ATTR my2STD =
standard_deviation from 59 days before SPX last_data_day to SPX last_data_day
of 1 value % move of Close of SPX
# this says calculate 1 STD using the last 60 days of daily % moves
SHOW
price_spx: Close of SPX
MEAN: myMean
2STDabove: myMean + my2STD*2
2STDbelow: myMean - my2STD*2
when
date is within 60 days
Resulting Chart:
How are Heating Degree Days and Cooling Degree Days calculated for
the USA Regional symbols located at: Root>Other>Geophysical>Geo_N.Amer>Geo_USA.Regional?
An explanation of Heating Degree Days and Cooling Degree Days can be found at:
http://www.cpc.ncep.noaa.gov/products/analysis_monitoring/cdus/degree_days/ddayexp.shtml
Below are the population weights used for the CoolingDegreeDays and HeatingDegreeDays columns for these Morningstar symbols.
NEW ENGLAND POP WT
CONNECTICUT 0.24461
MAINE 0.09157
MASSACHUSETTS 0.45603
NEW HAMPSHIRE 0.08876
RHODE ISLAND 0.0753
VERMONT 0.04373
MIDDLE ATLANTIC POP WT
NEW JERSEY 0.2121
NEW YORK 0.47833
PENNSYLVANIA 0.30957
E N CENTRAL POP WT
ILLINOIS 0.27504
INDIANA 0.13466
MICHIGAN 0.2201
OHIO 0.25142
WISCONSIN 0.11878
W N CENTRAL POP WT
IOWA 0.15211
KANSAS 0.13975
MINNESOTA 0.25572
MISSOURI 0.29085
NEBRASKA 0.08895
NORTH DAKOTA 0.03338
SOUTH DAKOTA 0.03924
SOUTH ATLANTIC POP WT
DELAWARE 0.01514
FLORIDA 0.30872
GEORGIA 0.15813
MARYLAND AND DC 0.11336
NORTH CAROLINA 0.15549
SOUTH CAROLINA 0.0775
VIRGINIA 0.13673
WEST VIRGINIA 0.03493
E S CENTRAL POP WT
ALABAMA 0.26124
KENTUCKY 0.23743
MISSISSIPPI 0.16711
TENNESSEE 0.33422
W S CENTRAL POP WT
ARKANSAS 0.08502
LOUISIANA 0.14212
OKLAHOMA 0.10974
TEXAS 0.66312
MOUNTAIN POP WT
ARIZONA 0.28233
COLORADO 0.23669
IDAHO 0.07121
MONTANA 0.04965
NEVADA 0.10996
NEW MEXICO 0.1001
UTAH 0.12289
WYOMING 0.02717
PACIFIC POP WT
CALIFORNIA 0.7843
OREGON 0.07922
WASHINGTON 0.13648
UNITED STATES POP WT
NEW ENGLAND 0.0498
MIDDLE ATLANTIC 0.14189
E N CENTRAL 0.16151
W N CENTRAL 0.06881
SOUTH ATLANTIC 0.18516
E S CENTRAL 0.06089
W S CENTRAL 0.11247
MOUNTAIN 0.065
PACIFIC 0.15447
Back to Top
How are the Implied Volatility (ImpVol) and Historical Volatility (HistVol) columns calculated?
See www.crbtrader.com/support/options.asp
Commodity Charts: How do you create a custom formula?
Choose the formula radio button instead of series. The Commodity DataServer Symbol area should then be disabled and the Formula bar enabled. Then enter the formula in the box adhering to Commodity DataServer query language syntax.
An example...
IF HighTemp of HOUSTON.TX is more than 100 then 1 ENDIF
You can reference symbols in the Commodity DataServer worksheet by referencing their column letter.
An example...
25 value moving average of ( A - B )
Where A and B are valid columns in your Commodity Charts worksheet.
I don't see the "Commodity DataServer" Menu in Excel
Click on Tools, Add-Ins and check to be sure the entry mim_excel is checked. If does not exist in the list you must re-install the Commodity DataServer Excel Add-In software. Contact your local Morningstar Sales Office or Morningstar Client Support at: 1-800-546-9646 to get access to the software.
How do I remove multiple date columns from my spreadsheet?
When you are updating multiple data series in your spreadsheet you must choose the formatting option "Merge Date Columns for Adjacent Requests". This will ensure you receive only one data column followed by all the data you have requested. For more information on the Commodity DataServer Excel Add-In please see the Commodity DataServer Excel Add-in User Guide and the Commodity DataServer Excel Add-in Training Guide.
My machine hangs if I select the "X" on the output query window.
When you are in the RefEdit cell and the dialog is minimized, selecting the "X" will close down Excel. This is a Microsoft bug that is fixed in Office 2000. There is not a patch for Windows 97.
How can I contact Morningstar if I am having problems?
You can call 1-800-546-9646 to reach our Client Support group 24 hours a day. If it is outside normal business hours you may have to leave a message, but be assured, you will receive a return call shortly. If you are having problems with which you would like us to have certain information, you can send any information that is pertinent to your problem to: commoditydata-support@morningstar.com.
Can Morningstar provide "dead issues with no survivorship bias"?
The Iverson equity data that we receive includes a feed of 'dead companies' which have stopped trading. Many of these are currently in our offering except where there have been symbol conflicts over the past few years. The data is supplied on an 'as quoted' bases whereby the history has NOT been merged causing survivor bias. The S&P COMPUSTAT data also has the same data for the 'dead companies' included in their "Research Files".
How do I make live/online backups of the Commodity DataServer database?
Commodity DataServer server backups may be made with the server up and running. Users can continue to query the data, however, it is important to make sure the database is not being written to. For more information, see the appropriate section in the Commodity DataServer System Administration guide:
"Commodity DataServer Backups" section in the Commodity DataServer System Administration Guide, (Solaris Chapter)
"Commodity DataServer Backups" section in the Commodity DataServer System Administration Guide, (Linux Chapter)
What do the abbreviations for Platts Megawatt Daily codes stand for (i.e., FDt, TDt, MA, DA, etc)?
TDt - transaction date - when the transaction occurred
FDt - flow date - the dates that the electricity is flowing as per Megawatt Daily
DW - time frequency is daily/weekday - updates 5 days a week (M-F)
WA - time frequency is weekly
MA - time frequency is monthly - Monthly average
DA - time frequency is daily - updates 7 days a week
OPk - off peak hours as defined by Megawatt Daily
Pk - peak hours as defined by Megawatt Daily
Back to Top
How does Morningstar deal with storing corporate action or merger events?
Morningstar stores equities in an adjusted form and has all the historical adjustment factors stored. Front end users can see the adjusted data (which is our default) or unadjust the data as desired. Morningstar has a history of retired equities which are marked with the original ticker, exchange, and CUSIP, prior to trading cessation. We store all cash and stock dividends, earnings, market capitalization and shares outstanding as well.
Back to Top
Does Morningstar provide Off Peak/On Peak Averages for Electricity ISO data?
Morningstar offers the OnPeakAvg and OffPeakAvg column for PJM Real-Time and Day-Ahead data.
The data begins on 1/1/2004. It is available for both RT and DA symbols. Peak hours are from 7:00 AM to 11:00 PM (the hour ending 0800 to the hour ending 2300) prevailing local time. Peak days are Mondays through Fridays, excluding North American Electric Reliability Council (NERC) holidays. Off-peak hours are from midnight to 7:00 AM (the hour ending 0100 to the hour ending 0700) and 11:00 PM to midnight (the hour ending 2400) Mondays through Fridays; also, all day Saturdays and Sundays (the hour ending 0100 to the hour ending 2400) and North American Electric Reliability Council holidays. The following macros are also available - _OnPeakAverage and _OffPeakAverage,_2X16Average, _2X8Average,_5X16Average,_5X8Average ,_7X16Average ,_7X8Average
Back to Top
What does "flow data" represent?
"Flow data" refers to the price prevailing on the day the commodity flows to the customer. It's easy to think of gas flowing in a pipeline, but the concept is also used for electricity.
Gas and electricity "flow" 24 X 7 regardless of trading activity
Trading activity is typically limited to Monday thru Friday and does not happen on holidays
Because traders don't trade all the time, they use the trading days they have to trade for gas or electricity that "flows" in the future - typically next day but on Fridays for the weekend and on the day before a holiday for that day as well.
Publications like Platt's keep track of trading activity and report prices based on "trade" date. ICE.TRANSCO.65 is the "trade date" price and you will see it is a 5 day price.
Gas and power trades, however are for product that flows every day - often for up to a month of time. So when you invoice the gas or electricity you need a "flow" price - which is the trading price - applied to the days the gas/electricity flows - AKA - flow date data - in this case ICE.TRANSCO.65.FLOW - which is a 7 day series with prices generally one day ahead of the trade date series.
Back to Top
|
