Home Forums Main Forum Data Set Methodology

  • Author
    Posts
  • draker
    Participant
    Post count: 5
    #520 |

    I have recently downloaded a data set from an alternate STIR complex and would like to begin my own curve analysis. May I ask you for a procedural methodology to begin formatting my data set?

  • Curve Advisor
    Keymaster
    Post count: 612

    So here are the basics:
    1) you need to determine if there is any kind of “year-end turn” or other seasonal effects in the data. Some STIR contracts traditionally have a turn, and others do not. It has been a while since I looked at the other STIR markets, but you will quickly be able to tell if you graph the strip of 3 or 6 month butterflies and there is a noticeable seasonal pattern of kinks (especially in the long end, where there is less likely to be activity priced in). Also, “year-end” may not always fall on Z – for example, in Japan it is H. Once you identify and quantify the turn, you need to adjust the futures prices for them, to get a “turn-adjusted rate”. See the “Z turn” thread for more info on the turn. I would imagine the turn in most currencies right now would be very small to negligible, and for some it is just zero.

    2) After you have the turn-adjusted rate, I am a big believer in having a constant maturity curve. That is… I want to try as much as possible to compare apples to apples. ERH5-H6 spread 6 months ago was on a completely different part of the curve, and those prices may not be particularly relevant. And if you just look at ER2-ER6 spread, you will get massive jumps every time the contract rolls. So you need to have some way to compare each curve from the past, to the current day’s curve. The “correct” way to do this is to build a complete yield curve for each day in the past and back out what the 3 month futures would be with the same number of days to maturity. This may not be practical for you. I am not sure what if there are any data service providers do this for you, but as an approximation, you can just use some kind of linear interpolation on the points. For example, for yesterday’s curve, you interpolate all the rates forward 1 day (ie yesterday, the closes were 1 day longer until expiry than today, so you are trying to adjust the curve to be more comparable to today’s curve). You need to make sure your interpolation is robust, as not all IMM dates are 13 weeks apart.

    I am not sure what your level of technical expertise is. However, the above is not as difficult as it sounds, although you need to be reasonably proficient at Excel. Now you can put as much or as little statistical analysis into these processes. I knew quants who made up complex models for the turn and to get constant maturity curves, and I knew a huge swaps trader who basically just “eyeballs” the turn and just relies on his firm’s internal systems to calculate curves in the past. So there’s a little of everything that goes on. Obviously, the more work you put into it the better, but at the end of the day, using very “basic” methods, you will get close enough. The analogy I would use is that you can use a surgical knife to cut off the excess fat off a steak, but many times, just flicking it off with you finger is almost as good.

    I think I “basically” answered your question. Let me know what other questions you have.

  • Curve Advisor
    Keymaster
    Post count: 612

    Tip: when you set up your spreadsheet to adjust for the year-end turn, set up a separate cell/variable for each year. The turn does not always have to be the same every year. You should keep your spreadsheet/model as flexible as possible. You may use a fixed constant or some link to a formula for the turn. The markets generally do not appear to be taking into account the fact that there can be anywhere from 2-4 days in the turn. I’m not sure if that will change going forward, so that is why I like having the flexibility. I will discuss more later in the other thread.

  • Curve Advisor
    Keymaster
    Post count: 612

    With the “excess fat” analogy I am not implying having a good / accurate model is not important. For example, if you run trades off an algorithm, the accuracy is crucial. Data is a useful tool, and the better the data, the more useful it becomes. However, you can be successful looking at curvature with just a static snapshot of the current markets, and some way of looking at the the historicals for various trades. Tiger Woods using just an 8 iron in a round of golf will still beat me using the best set of clubs.

  • mjaws
    Participant
    Post count: 4

    I agree with all that Joseph has to say here, Galen Burghardt has done a lot of work in the interest rate space. I found this paper while researching the turn myself.

    http://www.jamesgoulding.com/Research_II/Eurodallar/Eurodollar%20(One%20Good%20Turn).pdf

  • Curve Advisor
    Keymaster
    Post count: 612

    That link seems to be dead. But it was just some kind of link error. So people may have to copy and past the link:

    http://www.jamesgoulding.com/Research_II/Eurodallar/Eurodollar%20(One%20Good%20Turn).pdf

    Thanks for sharing. I will figure out why links are not working.

    Also, thanks for reminding me… GB’s book, the Eurodollar Futures and Options handbook is basically considered the “bible” of ED trading. So that’s a pretty good book to add to your library – only $54 on Amazon. I’m actually surprised there has been no update in 11 years, but I guess EDs haven’t changed materially. It probably has more information than most people need, but if you ever have a question, there’s a good chance you can find the answer in there. Or you can just come here – although I probably skimmed though the half I didn’t need. 🙂

  • Curve Advisor
    Keymaster
    Post count: 612

    assuming you are doing this in Excel, you should have a reference sheet in your workbook with the various IMM dates. You may also want to reference the next and previous IMM dates. That will help you determine when you need to adjust the interpolation (to account for the skips in prices after the contract roll).

  • Curve Advisor
    Keymaster
    Post count: 612

    after you are “done”, make sure you take a good look at the interpolated data to make sure it makes sense. In particular, there should be mo gaps when the contract rolls. So if ED5 sells off 10 bps, you have to make sure on your interpolated data, it show up as something very close to 10 bps. You can also graph it to make sure it looks reasonable.

  • William Sloan
    Participant
    Post count: 12

    Joesph,

    I have read through this thread (as well as the others) multiple times. I have not had as much time as I would like to sit down and really think about it. I want to attempt building a spreadsheet of the constant maturity curve (I enjoy excel), but I do have some questions. If my questions are a result of me not thinking enough about what info is already provided, don’t hesitate to tell me to keep my thinking cap on a little longer! 🙂

    What day do you use as your first day? The first data point of the set? or could it be any point of reference and just interpolate in both directions to create the time series?

    Does creating a time series of a constant maturity curve allow for the roll down effect to be quantified? (my guess is yes?)

    Is the roll down constant for each contract or variable? If it is variable, is there a way to extrapolate it out in time?

    Are both the structure values and market price levels used in your regression derived from creating the constant maturity time series?

    I really want to work at this and get a good understanding of it because I have an idea on how to maybe improve on your method of using regression analysis on the structures and market prices.

  • Curve Advisor
    Keymaster
    Post count: 612

    I will keep my answers brief, in case others want to chime in.

    * all ED futures eventually settle to the 3 month libor fixings (done every morning). Use the libor fixing to determine rates before the first ED contract.
    * “roll down” is not something that is “guaranteed” – it’s just an indicator of how much a structure will move if time passes and the curve shape remains the same. It is actually just a static measure that does not rely on historicals – you only need the current day’s closes to calculate it. I suppose it could be interesting to see how the rolldown changes over time, but that is not something I look at (as I am not sure what the value of that may be). I’m not sure if I am answering your two rolldown questions.
    * Yes – The scatter plots I show uses constant maturity curves. As the poster in the other General Questions thread mentioned, if you do not, you can get gapping.
    * The point of the scatter plot is to show how the price of the structure changes with the level of rates. I automatically have Excel fit a second order polynomial to the dots as a visual aid, but I would not say I rely on regression heavily.

    My general philosophy is that the data is one part of the equation, but the other is understanding what is going on in the markets. This is especially important now because the trading environment is constantly changing, and so the historicals may become less relevant. Let me know if you have any follow-up questions.

  • William Sloan
    Participant
    Post count: 12

    Just thought I would post this incase anyone wanted some free historical data to play with:

    https://www.quandl.com/c/futures/cme-3-month-eurodollar-futures

    The data isn’t perfect but it’s free and easy to access.

  • Curve Advisor
    Keymaster
    Post count: 612

    Thanks for that. That’s great resource.

  • William Sloan
    Participant
    Post count: 12

    Would it be possible for some one to elaborate a bit more on the procedures that need to be done to create a historical data set of a constant maturity curve?

    I not sure if the goal is to calculate the yields of each of the ED contracts based on 1$ invested today at the stub rate and then reinvested down the curve at today’s rates? Or if you are trying to set all of the contracts as if they had the same number of days to expectation?

    Any insight would be much appriciated!

  • Curve Advisor
    Keymaster
    Post count: 612

    Assuming you are doing this on some kind of spreadsheet:

    * assume you have a columns of EDx historical data, where “x” is the contract number and each row is a different date. Assume the data is in reverse chronological order (current rate is on top).
    * assume today’s ED1 price is for a contract that is 60 days to settlement. The goal is to get the data in all the other rows in that “ED1” column to represent an ED-equivalent price for a hypothetical contract that is 60 days to settlement.
    * yesterday’s ED1 price is for a contract that is 61 days to settlement. So you need to somehow estimate what a reasonable price would be for a contract that is exactly 60 days to settlement.
    * one simple but crude way to do this would be via using linear interpolation. You know what the 3 month libor cash fixing is on a given day, so you can interpolate between the two points (i.e. multiply the difference in rates by 60/61 and add to libor rate, etc). This method has some drawbacks, but unless you are planning to write some sort of algo, it should be fine for most purposes – especially when the curve is flat, and reasonably smooth.
    * eventually, you should have a column of data that approximates what the rate for a contract that is 60 days to settlement in all rows.

    Let me know if you have any other questions.

    • gudkaj1
      Participant
      Post count: 2

      Thanks Joseph

      Just trying to get a handle on this methodology.

      So if I wanted to convert data for ED2 (Mar15) to constant maturity would I still use Libor as my first point or would I use the closing price of ED1 (Dec 14) in order to linearly interpolate? If it is the latter can you explain how the number of days between dec 14 and Mar 15 IMM dates and the number of days to settlement for Mar 15 feature in the interpolation? Many thanks!

      • Curve Advisor
        Keymaster
        Post count: 612

        @gudkaj1 said:
        So if I wanted to convert data for ED2 (Mar15) to constant maturity would I still use Libor as my first point or would I use the closing price of ED1 (Dec 14) in order to linearly interpolate? If it is the latter can you explain how the number of days between dec 14 and Mar 15 IMM dates and the number of days to settlement for Mar 15 feature in the interpolation? Many thanks!

        You would use ED1 to interpolate. You should have a reference sheet in your workbook somewhere that has all the IMM Dates. The IMM dates are the third Wednesday of each month (or every third month, if you are looking at the quarterly ED contracts). Below are the IMM dates for the next 12 months:
        Wed 11/19/14
        Wed 12/17/14
        Wed 01/21/15
        Wed 02/18/15
        Wed 03/18/15
        Wed 04/15/15
        Wed 05/20/15
        Wed 06/17/15
        Wed 07/15/15
        Wed 08/19/15
        Wed 09/16/15
        Wed 10/21/15
        So there would be 91 days between ED1 and ED2. You should have a robust formula that determines the number of days between IMM contracts (ie you should not have to manually input the number of days between contracts).

        Note: I should have been more clear, but the libor cash fixing settles in 2 days. So the rate corresponding to the ED contracts fix on Monday mornings (2 days earlier), for settlement on the Wednesdays. So you need to adjust the libor cash fixing date when you interpolate.

      • William Sloan
        Participant
        Post count: 12

        So if you are using the closing price of ED-1 to interpolate to for converting ED-2, then are you trying to set all of the historical data of ED-2 to 91 old since that is the time difference between ED-1 and ED-2?

      • Curve Advisor
        Keymaster
        Post count: 612

        @William Sloan said:
        So if you are using the closing price of ED-1 to interpolate to for converting ED-2, then are you trying to set all of the historical data of ED-2 to 91 old since that is the time difference between ED-1 and ED-2?

        No. If ED2 is say, 220 days to settlement, you want all the data in the column for “ED2” to be 220 days away, as per my post #700. Yesterday, the raw data for ED2 will be 221 days from settlement, so you would use ED1 to interpolate to the rate when you are 220 days from settlement.

  • William Sloan
    Participant
    Post count: 12

    Thank you for explaining that to me. It’s amazing how difficult something seems until you understand it, then it is so simple.

    Out of curiosity if you wanted to do something more complex than a linear interpolation, what would an example of that be? Would it be using a more complex form of interpolation such as logarithmic, a spline, or something even more complex?

    Thanks again for that explanation!

  • Curve Advisor
    Keymaster
    Post count: 612

    Well, complex does not always mean better. I think each method has pros and cons. For linear interpolation, you could be underrepresenting the curvature between points. Not sure what benefit we would get from logarithmic interpolation. As for spline, you could be adding in curvature that is not there. Keep in mind that sometimes, you can get “funny” settles in EDs.

    At JPM, they built a daily curve using the various libor fixings, ED rates and swap rates (as you need the latter for points further out the curve). But even there, I’m not so sure they did a great job because many days the curve was noticeably kinked.

    I forgot to mention, there is a time mismatch when you use libor fixings and EDs (since one is fixed first thing in the morning and the other settles at the end of the day).

    How much detail you choose to incorporate is up to you – you just need to weigh the pros and cons.

  • Curve Advisor
    Keymaster
    Post count: 612

    @William Sloan said:
    So the way to do the interpolation for converting the raw data in column ED-2 is the same as was done ED-1 except for instead of using today’s LIBOR rate, you would use the data point that is the same number of days from expiration as the most recent ED-2 data point from the converted ED-1 data in place of LIBOR? Sorry for being so dense at getting this lol.

    We want all the data in the new column ED2 to be the same number of days away. So if ED2 is 220 days to settlement, yesterday the raw data for ED2 will be 221 days from settlement, so you want yesterday’s ED2 data (in the new column) to be 220 days from settlement. So you would take 90/91 of the difference between the raw ED1 and ED2 data (and add it to the raw ED1 data) to get your interpolated ED2 data (that is 220 days from settlement).

  • William Sloan
    Participant
    Post count: 12

    Joesph,

    Thank you for all the help trying to get me to understand how to convert ED data to constant maturity!

    I have to admit I still do not follow your methodology, but from thinking about the problem and the stated goal (remove the effects of time from the data so that all contracts would be valued as if all of the data points had the same number of days till expiration) I tried to come up with my own approach of making time decay linearly and have the most recent point converge on to the current values. I am going to try and attach the method I used to see if I am on the right path or not.

    Attachments:
    You must be logged in to view attached files.
  • Curve Advisor
    Keymaster
    Post count: 612

    I am not going to make a habit of looking at everyone’s spreadsheets (for numerous reasons, including it’s not time-efficient and it generally does not benefit anyone else). In fact, it’s probably unlikely I do this again, except in case of emergency. But I took a look at yours and here are some things you should be thinking about:

    You are on the right track, but …

    THINGS THAT YOU SHOULD FIX:
    * I do not think you turn-adjusted the data
    * you should have a column for the 3 mo libor cash fixing
    * rather than using the 3 month spreads that are fixed (row 2 of your sheet) for all days, you should use the 3 month spreads for each particular day. For example, instead of using af17=17.5, you should use “e348-d348”, etc.
    * the data around the contract rolls seem to be a little off – from 9/12 to 9/15, the current ED4 (EDU5) rallied 1.5bps, but your ED4 CMT column shows a drop of 6bps. This is probably one of the better ways to check your results. In the CMT columns, the day to day changes should not be materially different from you looking at a column of EDU5 changes. Because at the end of the day, the changes in the prices of the CMT columns should be about the same. So going forward, that is a good sanity check for you to see if you did it right.

    OTHER SUGGESTIONS FOR IMPROVEMENT:
    * you want to think about doing the least amount of manual adjusting as possible. So to update your sheet should only involve you putting the new data in.
    * you generally want to create a robust formula where the formula is the same in the entire column. It’s okay to have columns called “# days to settle,” “# days in IMM”, “next IMM date”, etc,
    * it’s okay to use multiple sheets. So put things you may refer to (like the IMM dates) on a separate sheet.

    I did not look at “everything”, but I think the above should help you get very close to where you should be.

  • Curve Advisor
    Keymaster
    Post count: 612

    I just wanted to emphasize that the last bullet in the “things you should fix” above is very important. In the theme of “teach a man to fish”, at the end of the day, the day-to-day changes in each of the constant maturity prices you calculate should be almost exactly the same as the day-to-day changes in the individual contracts (ie edz5, edh6, etc). If you are off from the actual day-to-day changes by more than a small fraction of a basis point, you probably did something wrong.

    • William Sloan
      Participant
      Post count: 12

      Thank you very much for taking the time to look at that and providing feedback! I think I have a pretty good handle on it now and thinking about how to lay everything out in an efficient manner. The difference in daily changes between the raw and adjusted data are fractions of basis points:). I am still thinking about different ways to calculate the turn values. I have done a few different interpolation techniques and the difference in the results are less than a half tick or so, so I guess its just picking the one that seems to be the most logically consistent. Thank you again for taking the time to look at that for me!

  • Curve Advisor
    Keymaster
    Post count: 612

    You’re welcome. It gave me an opportunity to give that great “fishing” advice, which I would not have thought of otherwise. Also, the participation rate is low, so I’m glad I could help an active member.

  • me
    Participant
    Post count: 27

    Maybe I’m doing something wrong here with the interpolation.

    Say we start again with your example, of today (H5 conveniently also has) 60 days to maturity, yesterday there were 61. If we work back to the beginning when the current contract became active, for each day we multiply the difference between the Cash USD LIBOR Fixing for that day and the settlement of the Eurodollar Futures by 60/61, 60/62…. until 60/91, the first day H5 became prompt.

    So if we got back one more day, now the active contract is Z4 (not H5) and we’re 5 days left to the fixing. What happens here? Is the multiplier now 60/5? This actually makes the jump in the roll worse.

    Please advise.

  • Curve Advisor
    Keymaster
    Post count: 612

    The key is, you want all past data points to be 60 days to maturity. So for this to happen, you may sometimes need to interpolate between two different contracts. If EDZ4 has 5 days to maturity, you would then need to use EDZ4 and EDH5, rather than the fixing and EDZ4.

  • me
    Participant
    Post count: 27

    Hmm OK. So why do we need to keep a time series column of the 3 month fixing (Post 768)? I think this is what was throwing me off.

    It sounds like what you’re saying is that formula in the ED1 column must interpolate between the cash fixing and the future, only for the current prompt contract? And then afterwards interpolate between contracts. And effectively ignore the data in the 3 month column.

    • Curve Advisor
      Keymaster
      Post count: 612

      You need the 3 mo cash fixings to calculate on average 50% of the first contracts. The first contract is not particularly important now, but if the Fed actually hikes later this year (or is priced to), then the first contract will start becoming more important. So important in fact, that you may want to build an additional sheet that is more thorough than linear interpolation for the first 6-12 months (I may start a new thread later in the year). But in any event, you should build your worksheets to be more robust now, while you are at it. It’s not that much more work. And the cash fixings may become more useful later in the year to look at, so having them on-hand could be useful.

  • me
    Participant
    Post count: 27

    I drew some yield curves by hand and I think I understand the problem better now, but do not necessarily have a good solution.

    Basically, it seems to me an issue of non-linearity of the rolldown of the future, or maybe put more precisely, using the slope of a small number of days to estimate the roll up the yield curve of a large number of days. For example, at the extreme, we’d be using, 5 days of spread between cash and future to estimate what it would be like for 90 days at the beginning of a roll.

    So what you’re saying is that we should interpolate forward between cash and future for the most recent prompt (backwards), and then always interpolate between future and future (forwards) for all successive ones. Will that create sort of break or inconsistency in the data since the interpolation methods are different before and after the first roll? Doesn’t seem like there is any other work around.

    • Curve Advisor
      Keymaster
      Post count: 612

      I don’t see any inconsistency in using the cash fixings and the futures, because the futures settle exactly to the cash fixings (on the Mondays before the IMM dates). So if you picture a 3 month libor cash fixing curve going 10 years into the future, the cash fixings for the Mondays before the IMM dates will exactly equal the rate implied by the ED futures. So you are comparing apples to apples.

      If there is any “error” in comparing the cash fixing to a future, I believe it pales in comparison to the error of using linear interpolation to begin with, so I think what I have proposed is reasonable as an approximation.

  • Curve Advisor
    Keymaster
    Post count: 612

    Keep in mind linear interpolation is just an estimate – history is only a guide anyway, so if a fly is a bp off here or there, that’s perfectly reasonable. The best thing to do is to actually build a curve each day, using the ED data. However, this is very data/programming intensive. In terms of what is best to do when using linear interpolation, keep in mind the following:

    The goal is just to give you something that will give you a good idea of how the structure has been trading. The key is, when you calculate flies using your interpolated data, it must be reasonably close to what the underlying contract does on that date – both in terms of level and change on day. So that is a good check on your data.

  • Curve Advisor
    Keymaster
    Post count: 612

    For people who have gotten a bit far with this and have questions about linear interpolation. I don’t have a “this is best” answer – it really depends on how much work you want to put into it. There are 84, 91, or 98 days between IMM dates. So you may have a slight mismatch between days in some of the contract periods. You have several options:
    * It is not out of the question to interpolate based on the actual day counts – you would just need to set up an extra sheet of day counts for each day for the contracts in the ED strip.
    * Normalizing the space between contracts is not terrible (i.e. assuming the daycounts between periods is 91 days, for simplicity). About 90% of the time, there will be 91 days, so this is not unreasonable. The rest of the time, you are possibly making a 7.7% error over some 3 month spreads (currently < 15% of 3 mo spreads), but if you look at 6 mo spreads, the error is only 3.9%, and 1.9% over a year spread. And the latter errors can be less if you have a 84 day period followed by a 91 day period. * Just doing it by some subset of today's day count is probably fine too, but you need to be careful you are picking out the two correct contracts to interpolate over when you go back. Note again that linear interpolation is just an estimate to begin with, and all you want it to do is to give you an idea of where things are. Keep in mind 3 month double double flies are going to magnify even the smallest errors in your spreadsheet. If you look at larger structures (like 6 month flies, 1 year flies or year spreads, etc), you will need less granularity.

  • me
    Participant
    Post count: 27

    OK. I admit this was harder than it first seemed.

    So just for clarity, there are two scenarios in the data, which require different interpolation points.
    1) When you need to bring forward the number of days to expiry (e.g., making 61 days to expiry 60 days). For ED1 I’ve interpolated between the Cash to ED1
    2) When you need to push back the number of days to expiry (e.g., making 2 days to expiry 60 days). For ED1, I’ve interpolated between ED1 to ED2.

    I’ve only done this for ED1, but I would assume the same logic would apply for the subsequent generic contracts. That is for ED2, when you need to bring forward the number of days to expiry, you’d interpolate between ED1 adn ED2. Similarly, when you need to push back the number of days to expiry, you’d interpolate between ED2 and ED3.

    Is this generally correct? Here’s a screenshot comparing the interpolated data and the raw data. I looked at the magnitude of differences, and they’re generally correct. I have not adjusted for the turn-rate. But there are cases when the RAW ED1 is pinned for several days, and ED2 is moving, and thus the interpolated data is moving. Additionally, the roll was very steep in December, so the smoothing around there can make the differences not as consistent.

    Just wanted to get a check here before I went too far down the (hopefully correct) course.

    Attachments:
    You must be logged in to view attached files.
    • Curve Advisor
      Keymaster
      Post count: 612

      If I understand you correctly, the approach you have seems to be correct. For ED(x), there will be times you want to interpolate between ED(x-1) and ED(x), and other times where you want to interpolate between ED(x) and ED(x+1).

      The chart you provided seems reasonable. Earlier after the contract roll, you would expect the interpolated values to be higher (lower rate), and later after the contract roll, you would expect the interpolated values to be lower (higher rate) in the current rate environment. Also, I think you can see the value in interpolating because had you used the non-interpolated values, you are subject to more noise in the data. I would imagine the non-interpolated data, if we continued further to the right would get even spikier than the interpolated data.

    • Curve Advisor
      Keymaster
      Post count: 612

      I forgot to mention… you need to turn-adjust before you interpolate.

  • Milan
    Participant
    Post count: 2

    Joseph, thanks for all your great input on the matter. After following your advise and the information from some of the other participants in this thread i think i got the hang of it and i’m almost done with my file. A few questions, though. You mentioned you use 0.65 for the next few turns. What do you use for z8 z9 z0? Also, how do we find how much were the past turns. I would like to add those values to my sheet in order to turn adjust properly.

  • Curve Advisor
    Keymaster
    Post count: 612

    @mmgalabov said:
    Joseph, thanks for all your great input on the matter. After following your advise and the information from some of the other participants in this thread i think i got the hang of it and i’m almost done with my file. A few questions, though. You mentioned you use 0.65 for the next few turns. What do you use for z8 z9 z0? Also, how do we find how much were the past turns. I would like to add those values to my sheet in order to turn adjust properly.

    There is a separate thread called the “The Z Turn” and I am currently in the process of answering some reader questions on that topic. Check that out, and post any additional questions in that thread. Thanks.

  • Curve Advisor
    Keymaster
    Post count: 612

    In finance, there tends to be an over-emphasis on historicals. I’m not saying there isn’t value in learning from historicals – clearly high frequency traders pick up patterns from poring over the historical data and generate super-consistent profits on a daily basis. Some technical signals (which are based on historicals) can also be very useful. So I think there is a natural inclination to have this “historical spreadsheet” be the main focus of trading. But…

    This spreadsheet is just a tool that summarizes the past data, and not all my trade ideas originate from it. In fact, I would say I IGNORE the majority of highs, lows, and other signals I get from my historical spreadsheet. When thinking about trades, there are THREE time frames you need to think about: the past, the present and the future. I wrote a post in the Forum about thinking about the past, present and future curves (“How Rich or Cheap” thread). So take a look at that thread next.

    What I do like about the historical spreadsheet is that it helps you understand how something has traded in the past. This could be very important if you are new to curvature trading. I find it useful at times – while I look at the markets every day, I’m not looking at every structure at all times. So having the tools to get historical confirmation is useful. Just keep in mind that the historicals are just a part of the bigger picture. You probably don’t want to rely just on the past. Ideally, you should have some corroboration between the past, the present and the future.

  • Oliver
    Participant
    Post count: 5

    <cite>@Curve Advisor said:</cite>I posted the above chart mainly just to show that a spread has some correlation to the level of rates in this environment. In all my charts, the last close is shown as a black dot. So I thought to myself, that the point looked very low, compared to the level of rates, and I wondered if there was a trade here… basically, I would want to sell ED9 vs some weighting of ED5-13 spread. This is what one would derive from “basic” historical analysis – you look for charts where the current point is at an extreme to the historicals.

    Hello,

    I was wondering if you had an easy way to collect such data for your scatter plots using BBG? It’s easy enough to get Historical data for a certain spread, say Z6Z7, but how do you get data easily for a spread over several years. For example if I wanted to track ED1 vs ED5 without having to manually adjust each time we roll into a new contract? I’ve seen similar instances where a trader posts the prompt vs 12m forward curve over several years but never figured it out.

    TIA.

  • Oliver
    Participant
    Post count: 5

    I realise you discuss the scatter plots and other graphs in the ‘Data set methodology’ thread. Perhaps my Q (inc this) is best moved to there.

    As an update, I spent some time toying with BBG and pulling data in using the excel add-in. If anyone finds it useful, I found a way to pull data using a semi-quick method. The following was done on the QSA product (ICE Low Sulphur Gasoil) but can be applied to any comdty curve.

    =BDS(“QSA Comdty”,”FUT_CHAIN”,”CHAIN_DATE=20140701″)

    It then produced a full chain of contracts that have traded since 01/07/2014 (the inception of the new LSG product).

    I then added dates (month start) at the top and used the formula:

    =BDH($C2,”PX_LAST”,E$1) to get prices to pull for each date. Below you can see an example. I then tidied the data up so that QS1 was correspondent to each prompt contract (shifting them up 1 month on a monthly basis). Now I am just trying to get it all into a scatter plot.

    Attachments:
    You must be logged in to view attached files.
  • Curve Advisor
    Keymaster
    Post count: 612

    I moved your questions to this thread, since it made more sense. I’m not sure what you are asking, so if these aren’t the answers you were looking for, please clarify:

    * Collecting Z6Z7 data for several years. This is the topic of this thread – how to get the raw data and turn it into a constant-maturity data set. I believe Bloomberg has a setting that attempts to calculate this for you (ask your rep). If they do, I’m not sure how accurate it is for Eurodollars, since it is probably more designed for something like stock futures (so the charts don’t look so disjointed). But let’s assume there is no Bloomberg setting and you want something simpler. What you want to do is collect the data for “ED2 Comdty” and “ED6 Comdty”. This will give you the historical data for the generic second and sixth ED contracts. You can then take this data and calculate the ED2-ED6 spread. Since the contracts all roll on the same day, when you calculate the spread, the resulting number will be “reasonable.” There will be a small “jump” on the day of the roll. But since it is a spread, this may not be too misleading – especially on a flatter curve, or if you go out further on the curve.

    * As for the scatter plot, it’s one of the standard chart formats in Excel (along with column, line, pie, bar, etc). You typically want to have a column of data for the x-axis (usually level or slope of rates) and you would use your ED2-ED6 spread calculation in the y-axis. If you want to have a black dot represent the current day, you just need to put a second data series in the graph (similar to how you would graph two lines on one line chart in Excel), but with just the previous day’s data. If you want to get really fancy, you can put multiple data sets (where each data set represents a different year, and you can color code them). There is a lot of flexibility.

    Let me know if I didn’t answer all your questions.

  • Oliver
    Participant
    Post count: 5

    Thanks Joseph it’s been a great help,

    Point 1 was surprisingly helpful, even after all these years I didn’t know you could use generics like that past ED1, so that has saved me a great amount of hassle! I know have the data pulled in a much simpler way.

    Point 2 I’m having trouble with trying to get too much data in one graph it seems. I basically want to show a historical relationship between ED1 and several other different spreads, but I think the only way to get excel to cooperate is to add a new legend series for each spread which is pretty time consuming, otherwise the data just looks odd.

  • Curve Advisor
    Keymaster
    Post count: 612

    Providing an example (or more description) of your finished product would help, as I am having trouble picturing what you are looking for. I thought I would just take a stab:

    I’m not sure how you have your data set up, but you can easily graph an entire table of data. Typical set-up would be you have each row be the data from a particular day. The first row would contain your data descriptive titles. The first column would be your x-axis and each subsequent column (you can plot many structures at once) would be the various structures you would like plotted (vs the x-axis variable). You then select the entire table and select the type of scatter plot you want. So it’s relatively quick, if you arrange your data into a table first.

    Let me know if this did not answer your question.

  • Mike G
    Participant
    Post count: 20

    Hello Joseph,

    I’ve turn adjusted my data by using linear interpolation. The way I did it was to take the points surrounding each December contract interpolate. After turn adjusting my data, I calculated the constant maturity data using the following equation:

    cm.data = ( (cm.day – front.weight) * back_leg + (back.weight – cm.day) * front_leg ) / (back.weight – front.weight)

    cm.day = current constant maturity days (ie 100 means i am generating the 100 day constant maturity data point)
    front.weight = front leg days to expiration
    back.weight = back leg days to expiration
    back_leg = back leg futures settlement price
    front_leg = front leg futures settlement price

    So for example, if I have U17,Z17,H18, my front leg would be U17 and back leg would be H18. Z17 would be the point i will be interpolating.

    I utilized Quandl eurodollar data starting from year 2000 to start generate it. For my spot, I am using 3 month spot libor. To get spot price I am using 100 – 3M libor spot rate. The spot rate then is my DTE = 0 spot price.

    After this I’ve generated a couple of the scatter-plots that you generally plot for us. It would be awesome if you can generate something similar to check if my graph is correct.

    As 2017-02-16, the following are the active contracts days to expiration:

    GE.H17 GE.M17 GE.U17 GE.Z17 GE.H18 GE.M18 GE.U18 GE.Z18 GE.H19 GE.M19 GE.U19 GE.Z19 GE.H20 GE.M20 GE.U20
    25 123 214 305 396 487 578 669 760 851 942 1033 1124 1215 1306
    GE.Z20 GE.H21 GE.M21 GE.U21 GE.Z21 GE.H22 GE.M22 GE.U22 GE.Z22 GE.H23 GE.M23 GE.U23 GE.Z23 GE.H24 GE.M24
    1397 1488 1579 1670 1761 1852 1943 2041 2132 2216 2314 2405 2496 2587 2678
    GE.U24 GE.Z24
    2769 2860

    I wanted to graph the GEH18-M18 3 month spread against the GEH18 outright prices. So I used CM data that had 396 and 487 days to expiration to generate the fly and plotted it against the 396 CM data. I graphed it from 2015-1-02 to 2017-2-16

    fly = CM396 – CM487
    outright = CM396

    I’ve attached the image. Does it look correct?

    Thanks,
    Mike

    Attachments:
    You must be logged in to view attached files.
  • Curve Advisor
    Keymaster
    Post count: 612

    This is my version of the chart. The differences look to be minor and probably comes from us using slightly different data sources, possibly turn differences and any slight formula differences. But I think this is somewhat close.

    Attachments:
    You must be logged in to view attached files.
  • Mike G
    Participant
    Post count: 20

    You mentioned in a few places about building a huge spreadsheet to monitor opportunities and I am thinking of taking on the challenge but am stuck on what information to start with. Would it be correct to assume that the huge spreadsheet you built was to find the best non-linear opportunities in Eurodollars? For example, the best structure would have good non linear fit. Are there any specific things you looked at in the “Work of Art” spreadsheet you built back then? Thanks, M

  • Curve Advisor
    Keymaster
    Post count: 612

    I recently wrote an article for Automated Trader magazine, on the importance of understanding the current regime we are in and how that may differ from the historicals. Historicals can be a useful tool, but some caution needs to be exercised. I would say a majority of the time, I think of a trade first and then see if there is historical back-up. However, about once a week, I’ll go through the sheets to see if there is anything that I may have missed. Here are some things you can start off with:

    * Look at say 3 mo, 6mo, 1 yr, 2yr, etc highs, lows and percentiles for various structures (single contract, spreads, flies, double flies, etc). You can even put what the 10th, 50th, 90th, etc percentile levels are. IMPORTANT: Just because something is at an extreme level does not mean that is a good trade. You need to think about the environment and how the structure can move in different environments.

    * Consider doing some secondary analysis. Once you have the data, you can do analysis on it like regressions and see how far the current point is from the fitted line/curve. Pick time horizons you think are useful. I suppose if you want to systematically look for structures with non-linearity, you could do that analysis as well.

    * That “Work of Art” spreadsheet also has a lot of analysis between various other countries (Euribor, Sht Sterling, etc.). The trader I did this for liked to trade the long end, so I also looked at weighted flies and regressions on flies vs the slope of the wings, level of the belly, etc.

    It will probably be a long data dump, so conditional formatting maybe useful to highlight the things you are looking for in the data. There’s a bit of a chicken and egg issue in building such a spreadsheet, because you probably won’t find much use in it unless you have some experience with interest rate structures, and you probably won’t trade much without having some tools.

    I print mine out at least once a week to look at. So I do find it useful. But just to set expectations, it’s probably not going to be a magic box that tells you how to make money… it’s just a tool.

You must be logged in to reply to this topic.