PC-SPAN - futures io
futures io



PC-SPAN


Discussion in Options

Updated
      Top Posters
    1. looks_one Dudetooth with 218 posts (213 thanks)
    2. looks_two ron99 with 115 posts (54 thanks)
    3. looks_3 CafeGrande with 30 posts (4 thanks)
    4. looks_4 BlueRoo with 24 posts (5 thanks)
      Best Posters
    1. looks_one Dudetooth with 1 thanks per post
    2. looks_two SMCJB with 1 thanks per post
    3. looks_3 ron99 with 0.5 thanks per post
    4. looks_4 BlueRoo with 0.2 thanks per post
    1. trending_up 119,818 views
    2. thumb_up 316 thanks given
    3. group 76 followers
    1. forum 608 posts
    2. attach_file 159 attachments




Welcome to futures io: the largest futures trading community on the planet, with well over 125,000 members
  • Genuine reviews from real traders, not fake reviews from stealth vendors
  • Quality education from leading professional traders
  • We are a friendly, helpful, and positive community
  • We do not tolerate rude behavior, trolling, or vendors advertising in posts
  • We are here to help, just let us know what you need
You'll need to register in order to view the content of the threads and start contributing to our community.  It's free and simple.

-- Big Mike, Site Administrator

(If you already have an account, login at the top of the page)

 
Search this Thread
 

PC-SPAN

(login for full post details)
  #201 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

I didn't think you were dismissing the ideas at all. As I said earlier if I can contribute I am more than happy to do so. Had a look at the backtesting code and can see where you are going. I have not tried to run any of it yet. I think I understand where you are coming from in terms of new trading ideas.

Selection of strikes within the tool has a defined approach. Deep OTM strikes with deltas lower than 5. I tend to lean more on probability statistics. Therefore the dashboard. Probability is important for me both at the start of a trade and through the trade as a management tool. The missing piece for me has been getting a better understanding of time decay and rate of change in time decay. The ability to get time series option chains provides the data required to do a number of time series charts that might help with understanding the characteristics of time decay. My approach to this is to exposure myself to the experience rather than the theory or conceptualization of time decay ideas. The time series data I can access through your file will allow me to view and render what actually happens in difference commodities. With coding I want to do this on the fly and on demand. I want to look at these perspectives in as short a time as possible. But I want to be able to do it often to increase the magnitude of the experience. To me the power of the tool is not so much finding new trading ideas but better understanding the animal I am seeking to tame. I greatly appreciate the role futures.io (formerly BMT) has had in expanding my experience and exposure.

From the screen shots I wish to pursue further the time series nature of the data. For example I want to see the last 90DTE of last months expiring contract for each of the commodities I am considering as candidates. Then over time this experience will build as a sample and there may be new insights discovered.

For example, one of the other coding tasks I have been working on is to look at price range into expiry for different periods and reference probability to historical and seasonal price range results.

What you see in the screen shots is not complex. The dash board is simply excel formulas. The inputs are entered on the fly directly from an interactive option chain generated from your scanner. The full option chain is then used as the basis to build the chart. From the Scanner to the dashboard to the chart takes less than a few minutes at most. So now I can visually see the vol smile, the fat tails and what strikes are in and out of probability.

If I sent you the code right now you would curse me because it still needs to be consolidated, tested and tweaked. And you would spend to much time trying to get it to work. But I am happy to email it to you to browse. But it is in no state to post here. If you PM me your email address I will send you the code. But maybe it would be better to wait until I get the times series module working to my satisfaction.

Reply With Quote
The following 2 users say Thank You to BlueRoo for this post:
 
(login for full post details)
  #202 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

BlueRoo - You've done some very nice work; I've seen commercial packages that are not that well designed. Bonus points for putting a visual representation of the StdDev ranges and position break-even points on the chart.

Couple of comments/suggestions.

- I noticed you have the Hoadley Options tools and it looks like you've calculated the probability of an option expiring worthless (perhaps you used HoadleyProbAtEnd). If you're so inclined, you might also use his formula for the probability of touching the strike at any time during the life of the option (HoadleyProbAnyTime1). As you probably know, the latter is often 2x the former, but it can vary beyond that rule of thumb with a higher IV and greater time to expiration.

- I believe the lower section of your dashboard (the probability section) was calculated using the standard normal (Gaussian) distribution. Depending on the input parameters, that may be close enough, at least to calculate one StdDev, but I'd strongly encourage you to use the lognormal distribution. That's the underlying distribution assumption for the models and programs the pros use, and whether or not it's the best one, it's a fact that prices cannot go below zero. You can adjust for skew, kurtosis, jumps and so on in more sophisticated models (and Hoadley has some examples), but you'll have a more accurate picture of probable price ranges if you switch to lognormal.

I hope I'm not sticking my nose where it doesn't belong. These are just friendly suggestions and there's no way I could replicate what you've done. My only expertise, such as it is, is that I started with the normal distribution, too, realized it wasn't the right one, and then spent way too long trying to get the correct lognormal calculations into Excel (it's been a long time since I sat in a statistics classroom).

Reply With Quote
The following user says Thank You to CafeGrande for this post:
 
(login for full post details)
  #203 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received


CafeGrande View Post

Couple of comments/suggestions.

- I noticed you have the Hoadley Options tools and it looks like you've calculated the probability of an option expiring worthless (perhaps you used HoadleyProbAtEnd). If you're so inclined, you might also use his formula for the probability of touching the strike at any time during the life of the option (HoadleyProbAnyTime1). As you probably know, the latter is often 2x the former, but it can vary beyond that rule of thumb with a higher IV and greater time to expiration.

- I believe the lower section of your dashboard (the probability section) was calculated using the standard normal (Gaussian) distribution. Depending on the input parameters, that may be close enough, at least to calculate one StdDev, but I'd strongly encourage you to use the lognormal distribution. That's the underlying distribution assumption for the models and programs the pros use, and whether or not it's the best one, it's a fact that prices cannot go below zero. You can adjust for skew, kurtosis, jumps and so on in more sophisticated models (and Hoadley has some examples), but you'll have a more accurate picture of probable price ranges if you switch to lognormal.


CafeGrande, I appreciate the post and your insight. Without your post I would not be pushed to review and learn. Thanks for the suggestions. As a result I have done the following...

See the new screenshot below. Includes normal, lognormal and hoadley probabilities. The previous screenshot only uses formulas with not hoadley. I didn't want to include them because possibly sharing the file and it not working for others. Anyway I have now because the discussion is about the goodness of fit of the option chain to normal distribution. Clearly from the chart it is not normally distributed. I have added descriptive statistics to further see this discrepancy. This is done for each variable option price, volatility and delta. This is exploratory. I am trying to better understand the behaviour of the asset. Observations: LogNormal Probability of expiring in the range is much larger. However hoadley probabilities are similar to normal. This is interesting to me and thankyou for pushing me to explore it further. In the case in question the strikes of the spread a pretty much where I would place the trade give everything else is constant. I like you realize lognormal is a better fit for the option chain. But I have not had enough experience looking a real world results to say if the difference provides a significant enough result to change strikes. In this case the hoadley probabilities may be the umpire. I would be interested in understanding more from your experience if you have seen a significant difference.


Reply With Quote
 
(login for full post details)
  #204 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

BlueRoo -

First, I don't know how you added all those elements since I posted my message; because of the time difference, you should have been sleeping most of the time. You must know this stuff like the back of your hand if you can do it that fast!

I am attaching an image that shows At Expiration, At Any Time, Prob of expiring in range, and Standard Deviations.
The Excel snip (shaded in light blue) is something I put together. It looks like there are some differences with your program. As background here's where my numbers came from:

The At Expiration and At Any Time are from Hoadley formulas and you can see the input parameters that I borrowed from your image. My At Expiration matches to two decimal places with a "test" source I use - a probability calculator from ivolatility.com (same calc they license to OptionsHouse, TradeKing, Scottrate and probably others). My At Any Time doesn't match precisely, which puzzled me for a while until I figured out that the Hoadley default is set to "continuous" price observations and that makes a difference. If you ratchet it back a little you can get it to almost match the ivolatility test calc (I don't know what ivolatility's settings are).

The standard deviations in my calc also match the test calc but they came from an el-cheapo ebook I bought online. I could not find them in Hoadley.

When I was putting the simple spreadsheet calc together I also tested it with the online Hoadley calcs but they restrict you to x number of calculations per day (it's fairly small).

I'll address your other comments in a separate post.

Attached Thumbnails
Click image for larger version

Name:	lognormal stuff.PNG
Views:	118
Size:	38.6 KB
ID:	146471  
Reply With Quote
 
(login for full post details)
  #205 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received


CafeGrande View Post
BlueRoo -

I am attaching an image that shows At Expiration, At Any Time, Prob of expiring in range, and Standard Deviations.
The Excel snip (shaded in light blue) is something I put together. It looks like there are some differences with your program. As background here's where my numbers came from:

The standard deviations in my calc also match the test calc but they came from an el-cheapo ebook I bought online. I could not find them in Hoadley.

CafeGrande,

The hoadley formulas in my sheet have expected return as zero. My Hoadley is over 10 years old as well. Might be the reason for the slight differences?!?!
My formulas for lognormal are definitely off! I need to have a play with them. Can you post the formula's in your cells for your log normal standard deviations. It might give me a clue to what I have done wrong.

Pretty fast in most software stuff, but the back of my hand is a little shaky at times. Need some trading buddies to help me keep on focus. Looking forward to your further response.

Reply With Quote
 
(login for full post details)
  #206 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

BlueRoo - I've attached the formulas for 1 to 3 standard deviations using the lognormal distribution. Rows 7 and 8 (I think) are used as a shortcut so the long formula in Row 7 doesn't need to be repeatedly typed in if you want to make 1.5, 2.5 etc SD calculations. In my little Excel calculator I keep it down at the bottom (not visible) but with a little bit of typing it looks like it could be eliminated.

Some notes on the input box (rows 2 through 5). You probably already know these, but someone else might want to use the formulas and this will save them some head-scratching:

1. "Mean" is Expected Return/Risk Free Interest Rate
2. "Time" is in fractions of a year. I changed it to DTE and adjusted the formula slightly (/365)
3. "St Dev" is expected volatility, I renamed it to avoid confusion with the output SDs

Attached Thumbnails
Click image for larger version

Name:	LN StDev calcs.PNG
Views:	105
Size:	35.1 KB
ID:	146577  
Reply With Quote
The following user says Thank You to CafeGrande for this post:
 
(login for full post details)
  #207 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

Update to Normal and LogNormal Standard Deviations. See screen shot below.
For Normal my formula is - =EXP(($E$8)*SQRT($E$6/365)*-1*NORMSINV(C25))*$E$5
For LogNormal my formula is - =EXP(($E$8)*SQRT($E$6/365)*-1*LOGINV(C25,($H$30/100),($J$30/100)))*$E$5
Removing the excel *INV function and reverting to what you posted you can see the slight differences.


Reply With Quote
 
(login for full post details)
  #208 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

So from the historic sheet I have coded the ability to create the attached chart on the fly.


Reply With Quote
 
(login for full post details)
  #209 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

(^ image above ^)

That's valuable! I don't know if you plan on archiving it, but in my experience it's useful to have that type of history, although it's expensive to procure.

It's easy and cheap enough to buy a time series for ATM IV, but everything changes when you want a particular option-delta-IV. Nice work!

I'm going to have to start drinking that Oil Can beer (Foster's). Must be something in it because you and Peter Hoadley are pretty darn smart.

Reply With Quote
 
(login for full post details)
  #210 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

Not sure what you mean by archiving the chart. Is that something you do on futures.io (formerly BMT)?

Yes it is the standard theory time decay curve that got me frustrated once because it did not seem to apply to OTM options. And then I discovered in was solely basted on ATM.

I am interested in pin-pointing the greatest rate of change. I am also interested in taking a sample of say 90 different option series 90DTE and observing what variance there is in decay. Then I start thinking does decay have different rates of change in different seasons?

Anyway without the work Dudetooth and others had done on the code I would not have been able to do this. I hope this contributes something new to the table.

Reply With Quote
 
(login for full post details)
  #211 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received


BlueRoo View Post
Not sure what you mean by archiving the chart. Is that something you do on futures.io (formerly BMT)?

I am interested in pin-pointing the greatest rate of change. I am also interested in taking a sample of say 90 different option series 90DTE and observing what variance there is in decay. Then I start thinking does decay have different rates of change in different seasons?

I meant archiving the data behind the chart. Will you store it in your program or in some other dbase? A number of options analysis programs contain what you've displayed, but once expiration occurs, it's quite often gone. The really expensive programs - $1000 or more per month - might archive it for you, but I've never tested them.

Since archiving options data can quickly consume a lot of space, you might consider excluding the nickle strikes that rarely have very much volume. They exist in silver, natural gas, grains and a few others.

Re your question about seasonal decay, I think it's there in weather-influenced physical commodities but I haven't quantified it. Examples: Assuming we've had a seasonal run-up in IV, it usually declines rapidly in the grains once we get to Jun/Jul; in coffee once the frost scare has passed; and in natural gas once we realize we're not going to freeze to death in late Feb.

Finally, I didn't mean to overlook Ron99 who came up with the idea, DudeTooth who did a ton of work converting the archaic !@#$% PC-SPAN into a very useful tool for beginners and pros alike, and those who provided ideas and feedback to DudeTooth when he was grinding it out in late 2013/early 2014 - they've all been big contributors.

Reply With Quote
 
(login for full post details)
  #212 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

CafeGrande,

Interesting resource for excel at https://www.rtrand.com. (Free probability AddIn).

Function Reference - NtRand

Yes, I have draft code to export the data for both the snapshot Option Chain Chart and for the time series Decay chart, that will allow the data to be saved as csv. Small data sets and small files so a few megabits of space will allow 1000's to be saved. It is understanding the behaviour that I am interested in and this can not be done by a single chart. Need a larger sample to make informed observations. But this is a start and already informs more general concepts for best DTE entry and exit for max daily ROI. My view is it has to be observed before it can be done.

Dudetooth as a copy of my messy code version of the span file now. Maybe some of this will find its way into a new version.

I have picked up from other posts you have done that you are view aware of how commodities can behave and be influenced. E.G. Just your comments about grain, coffee and gas. I find insightful. Maybe we can do some work in this area over time.

Reply With Quote
The following user says Thank You to BlueRoo for this post:
 
(login for full post details)
  #213 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

Dudetooth,

The new time series decay chart code is almost identical to that for the option chain chart in the file I sent you.

Yes, I had though about archiving snapshots in csv files. But if you have the psa files then there is not need. The only issue is the psa files seem to be very large, s a few years of data will equal approximately 12 gigabytes of space.

Yes, data collection. I have not tried to understand how you do this at this point in calcMargin or how the data is draw or transformed from the psa files. It must have been a huge task to do what you have done.

I have questions about the data. Should I ask you here or through futures.io (formerly BMT)?

Here is a quick list of the things I want to do.

1. Make the tracker sheet interactive with double click cell code. For example, I would like to be able to insert a header row. This will require reworking track all to accommodate header rows and blank rows. Second, I would like to be able to highlight and un-highlight a spread.

2. I would then like to be able to build a HV and IV Dashboard for all commodities for front and back months. This code would ideally run when a new psa file is downloaded.

3. Scanner criteria development. You will see I get the full option chain by simply changing the delta to 50 and minprice to $10. But I would like to be able to simply type in a option series code to get the full option chain. Then the second method of scanning I would like to explore is IV for options outside of a nominated 1 or 2 standard deviations. This is where I would need your help. I have looked at how I might deconstruct the calMargin into small routines and then be able to route the code based on a filter choice variable but I do not have clue where to start. Can you let me know if you are interested or willing to help.

4. I then want to consolidate all the code and forms with correct module assigned between front end and back end code.

This will then have a tool that I will use everyday and not have me going elsewhere for processing and monitoring and gathering.

4. Last, I am interested in finding the best trades. My approach is through study but I can see where you are going with the back testing and this code approach interests me.

Reply With Quote
 
(login for full post details)
  #214 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

BlueRoo,

I don't think there is any way around holding pa2 files right now, not unless we can find a way to access a compressed file programmatically (the closest I would have right now would be to unzip a pa2, read it, and delete the uncompressed when done). The pa2 files hold most of what you are looking for without any calculation necessary ... delta, IV, option price, futures price, and HV. Conversions are necessary to get $ amount and the decimal in the right place, but the only real calculation is for margin. If that is not needed for some of the data collection, that part may be able to be skipped.

To address the points in your quick list:

1) I've never played with double-clicking to execute VBA, but it looks doable. I believe I have an idea of how to have the code find the proper columns/rows dynamically to fill data, so the user can customize the layout.

2) HV and futures prices would be able to be grabbed easily, IV would require the entire option chain. It is possible that the same code used to scan could be used to extract the IV to create volatility charts.

3) These adjustments would be fairly straight forward. I'm sure that the calcMargin can be broken into pieces, I have made some adjustments in those regards, but it can definitely use more.

4) I'm all for streamlining the code, my biggest problem is that I sometimes don't fully understand the code I'm using ... I just know it does what I want it to do.

5) I think the back-testing that I was looking at is akin to your idea of looking at all of the options to see their behaviors. My way was just a little more like looking through a straw.

6) The delta numbering can be modified. In a pa2 file there is no decimals, so you just change what you divide/multiply by to get want you want. I thought seeing a delta of 2 rather than 0.02 was easier for folks to understand in the spreadsheet. Price quotes are a little different because the pa2 files have a mechanism built in to tell you where to put the decimal for the prices. It is done automatically, so to speak, within the code. This could be modified, but it adds a layer of complexity. I left it as is because I wanted to keep the code as simple as I could and if the info was in the pa2 files I wanted to use that. The spreadsheet should be showing the same prices that you would see if you were running it through the PC-SPAN program (notable exception, JY).

7) A big weakness with the pa2 files is that there is no volume or OI reported. I currently use another spreadsheet to strip data from Barchart for futures charts and option data, namely volume and OI.

They way forward may be to determine what to focus on and in what priority. It would seem that visualizing historic data would top the list, at least from my point of view. Scanners and trade ideas will likely flow after we have found those behaviors to exploit. I'll try to create a mock-up of a way to visualize some of what you are looking for and we can go from there.

Reply With Quote
 
(login for full post details)
  #215 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

Here is a screen shot of the popup box you get when you right click on anywhere in the tracker spread sheet. This work by left click a row you wish to modify and then right clicking. You can then insert a header row, remove a header row, insert a blank row, and, copy and paste a row (used to move a position to a different header group).

I am looking for any suggestions for improvements.


Reply With Quote
 
(login for full post details)
  #216 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

A couple of months ago I tried to get a better understanding of the historical volatility (HV) column in DudeTooth's application. I messed around for a little while but I couldn't match it with common HV periods such as 21, 42 or 63 trading days. Some times it was close but other times it was way off.

Since that time, I've ignored the column by graying it out or hiding it. I get HV data elsewhere and it wasn't a complete record because ICE does not supply data for the HV column.

Fast forward to today: I was trying to find an end-of-day ATM IV to automatically populate some probability worksheets. I figured that with a couple of attempts (iterations), I could accomplish this task with DudeTooth's application. I soon discovered that the CME SPAN HV column isn't HV at all, but rather ATM IV. See the attached snip.

This makes sense because IIRC the "complete" SPAN product (~$3000) is capable of complex risk analysis and modeling and you need an ATM IV for skew analysis and shifting.

Anyway, if my discovery is correct, for CME products no iterations are necessary, you have the ATM IV right in front of you. For ICE products, you will have to plug in the closest to the money Put or Call, but it might be an option with no open interest, in which case you'll have to move up or down a strike until you get a hit.

Attached Thumbnails
Click image for larger version

Name:	SPAN and IV.PNG
Views:	93
Size:	35.0 KB
ID:	148249  
Reply With Quote
 
(login for full post details)
  #217 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


CafeGrande View Post
I soon discovered that the CME SPAN HV column isn't HV at all, but rather ATM IV.

Thanks for figuring this one out. I could never find a definition from CME as to what their "Base Volatility" was and made the incorrect assumption that is was the HV of the underlying. Right after you posted this I just searched for "Base Volatility" without CME and found numerous sites talking about it:

The base volatility, or anchor volatility, is the average of all the implied volatilities of the options specified by the base volatility field and the curve definition. For example, if the skew definition uses calls and puts, two expiration chains, and the base is at-the-money, the base volatility is the average implied volatility of four options: the at-the-money put and call for both expirations.

The base volatility has significance during curve application. It is the point that moves the skew curve as the marketís implied volatility changes. When you save a volatility skew, you are saving a curve shape and an anchor (base) point definition. When you apply a volatility skew, the base volatility is calculated, and the skew curve is anchored to it. If the base is the at-the-money strike and the volatility of the at-the-money strike increases, the entire curve rises on the Y axis; similarly, as the at-the-money strike changes (which implies a change in the underlying price), the entire skew curve follows it along the X axis.


It looks you you hit it on the head.

Reply With Quote
 
(login for full post details)
  #218 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received


Dudetooth View Post
Thanks for figuring this one out. I could never find a definition from CME as to what their "Base Volatility" was and made the incorrect assumption that is was the HV of the underlying. Right after you posted this I just searched for "Base Volatility" without CME and found numerous sites talking about it:

The base volatility, or anchor volatility, is the average of all the implied volatilities of the options specified by the base volatility field and the curve definition. For example, if the skew definition uses calls and puts, two expiration chains, and the base is at-the-money, the base volatility is the average implied volatility of four options: the at-the-money put and call for both expirations.

The base volatility has significance during curve application. It is the point that moves the skew curve as the market’s implied volatility changes. When you save a volatility skew, you are saving a curve shape and an anchor (base) point definition. When you apply a volatility skew, the base volatility is calculated, and the skew curve is anchored to it. If the base is the at-the-money strike and the volatility of the at-the-money strike increases, the entire curve rises on the Y axis; similarly, as the at-the-money strike changes (which implies a change in the underlying price), the entire skew curve follows it along the X axis.


It looks you you hit it on the head.


That's a good find. We still don't know how many strikes the CME uses; some providers use the two nearest the money (one above and one below) and I've come across at least one that uses three (the nearest to the money plus one above and one below). It doesn't really matter though. I doubt any of us are trading 100 million dollar portfolios, so a few basis points in IV or even 10 or 20 is probably not going to change a trading or hedging decision.

A related benefit: For those that don't have access to daily ATM IV data and charts, they could build a history (and then charts) using your application.

Reply With Quote
 
(login for full post details)
  #219 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received


CafeGrande View Post
A related benefit: For those that don't have access to daily ATM IV data and charts, they could build a history (and then charts) using your application.

Code in draft...IV dashboard (not shown) and then you can generate a vol chart with price on the fly...

Reply With Quote
 
(login for full post details)
  #220 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received


BlueRoo View Post
Code in draft...IV dashboard (not shown) and then you can generate a vol chart with price on the fly...

Just out of curiosity, is all of the data on the chart derived from the SPAN files, i.e. have you collected several months of history and your application calculates HVs based on the futures settlement price found in the SPAN file?

Reply With Quote
 
(login for full post details)
  #221 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received


CafeGrande View Post
Just out of curiosity, is all of the data on the chart derived from the SPAN files, i.e. have you collected several months of history and your application calculates HVs based on the futures settlement price found in the SPAN file?

Yes all data is derived from the SPAN files. Generates chart in a few clicks based on your selected date range. It does not calculate HV. HV and IV are the fields named as such in the SPAN spreadsheet.

I have introduced functions for greeks that are calculated based on SPAN data but have not progressed it further...

...do you have a suggestion...

Reply With Quote
 
(login for full post details)
  #222 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

I was mostly wondering if you've had difficulty retrieving so many consecutive days of data. Currently, I don't store any history, but if it's a reliable source and it downloads fairly quickly, I might try storing a few contracts worth of individual option data to build a skew time series, either by delta or moneyness (percent out of the money)

As for suggestions, two minor ones:

- Since it appears the HV field is actually the ATM IV for CME products, you'll probably want to rename the label.

- I don't know how experienced you are with options, but I'd put a "real" HV calculation lower on development list. It's nice to have, but it's readily available in virtually all charting programs and if you're like most, you'll probably spend more time with ATM IV and option-specific IV than the underlying's HV.

Reply With Quote
 
(login for full post details)
  #223 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received


CafeGrande View Post
I was mostly wondering if you've had difficulty retrieving so many consecutive days of data. Currently, I don't store any history, but if it's a reliable source and it downloads fairly quickly, I might try storing a few contracts worth of individual option data to build a skew time series, either by delta or moneyness (percent out of the money)

As for suggestions, two minor ones:

- Since it appears the HV field is actually the ATM IV for CME products, you'll probably want to rename the label.

- I don't know how experienced you are with options, but I'd put a "real" HV calculation lower on development list. It's nice to have, but it's readily available in virtually all charting programs and if you're like most, you'll probably spend more time with ATM IV and option-specific IV than the underlying's HV.

The EOD files decompressed for me are about 66mb. Therefore a years worth of data would be 250 x 66 = 16,500mb. So you do need some storage capacity.

Once the files are downloaded...it takes about 3 minutes to build a year to date historical record for a select strike. Once you have this the vol and history charts I have posted are generated in a second.

Yes, following you and Dudetooth in my be better titled base vol...so if the BV is higher than the IV option premium is cheap and if lower expensive...anyway the way I use it currently is as a measure of comparison...tending to look for assets with above average IV...

Reply With Quote
The following user says Thank You to BlueRoo for this post:
 
(login for full post details)
  #224 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

DudeTooth -

If you're thinking about updating the SPAN tool some day, and if the data elements already exist in the risk arrays (I think that's the term for the big files from the CME and ICE), could you add a column for Gamma and Theta? I'd suggest making the columns easy to hide because I know they wouldn't be useful to everyone.

Currently, I sum the number of option contracts, the option value and the delta by product. If Gamma and Theta could be summed, the application, in addition to it's scanning abilities, would become an even better portfolio management and reporting tool.


Thanks!

Reply With Quote
 
(login for full post details)
  #225 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


CafeGrande View Post
DudeTooth -

If you're thinking about updating the SPAN tool some day, and if the data elements already exist in the risk arrays (I think that's the term for the big files from the CME and ICE), could you add a column for Gamma and Theta? I'd suggest making the columns easy to hide because I know they wouldn't be useful to everyone.

Currently, I sum the number of option contracts, the option value and the delta by product. If Gamma and Theta could be summed, the application, in addition to it's scanning abilities, would become an even better portfolio management and reporting tool.


Thanks!

CafeGrande,

Actually, I've been working with BlueRoo on some new ideas. Unfortunately, the pa2 files do not contain the Gamma or Theta (at least not that I have seen), but the idea of adding some Greeks has been tossed around and is definitely doable. We have even worked out a way to show only the columns of data that you want to see.

One area I have wanted to build up was the portfolio management. I kind of have an idea of what you are asking about, but if you don't mind elaborating, can you give me an example of how you are using the Gamma and Theta for your portfolio management?

Reply With Quote
 
(login for full post details)
  #226 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received


CafeGrande View Post
DudeTooth -

If you're thinking about updating the SPAN tool some day, and if the data elements already exist in the risk arrays (I think that's the term for the big files from the CME and ICE), could you add a column for Gamma and Theta? I'd suggest making the columns easy to hide because I know they wouldn't be useful to everyone.

Currently, I sum the number of option contracts, the option value and the delta by product. If Gamma and Theta could be summed, the application, in addition to it's scanning abilities, would become an even better portfolio management and reporting tool.


Thanks!

I have all the greeks in a version of the span file that can be easily added from the option trading workbook that is available free on the net it has all the greek functions and can be easily added with little vba skill

one idea has been finding low delta high theta options...i like your ideas and ask if you could email me so we can work together closer on them? it would help me better understand your thinking process better

Reply With Quote
 
(login for full post details)
  #227 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received


Dudetooth View Post
CafeGrande,

Actually, I've been working with BlueRoo on some new ideas. Unfortunately, the pa2 files do not contain the Gamma or Theta (at least not that I have seen), but the idea of adding some Greeks has been tossed around and is definitely doable. We have even worked out a way to show only the columns of data that you want to see.

One area I have wanted to build up was the portfolio management. I kind of have an idea of what you are asking about, but if you don't mind elaborating, can you give me an example of how you are using the Gamma and Theta for your portfolio management?

I'm trying to get the basics on one spreadsheet so I can see my cumulative positions across brokerages. Here's a snip of the Risk Navigator tool from Interactive Brokers. https://www.interactivebrokers.com/en/?f=%2Fen%2Fsoftware%2Fpdfhighlights%2FPDF-riskNavigator.php. There are a lot of features and data columns not shown, but what I'm trying to do (in Excel) is get a product-level view (where you see the "+" sign in the snip; I'm not aiming for drill down capability).

The IB tool is very useful but because of their margin policy on FOPs I'm slowly moving some money out of there. If the SPAN tool included the additive Greeks, I could have a simple dashboard in Excel and if I wanted to get fancy, I could do other things like link the SPAN tool to real-time underlying prices and re-calculate the Greeks as often as I wished, break out my risk by time period (probably DTEs in 2 week buckets or something like that).

I don't pay very close attention to Theta but I do watch Gamma closely, especially if the short options are near the money and near expiration. Gamma is probably not a big issue if someone is selling way OTM options (5 Delta or less) because they've likely closed their position once they're close to the money. Sometimes, though, I sell a 10 or 20 Delta option because I *want* to be that close to the money.

Edit: Please don't spend time on this on my account. I can calculate the missing Greeks in Excel by using the Hoadley add-in tools. I just thought that if they're already in the arrays, maybe they could be displayed in a future update of PC-SPAN.

Reply With Quote
 
(login for full post details)
  #228 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

BlueRoo,

I think I have the workbook you're referring to. FWIW, another resource from a well-known author is the "DerivaGem" software that accompanies John Hull's book(s).

DerivaGem Software

Reply With Quote
 
(login for full post details)
  #229 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


CafeGrande View Post
Here's a snip of the Risk Navigator tool from Interactive Brokers.

Thanks ... that's some good food for thought. I had already been looking at the idea of summing all of the risk/option value by product to get close to portfolio margin (minus the intra/inter-commodity spread adjustments). Now I'm intrigued to see the Greeks summed by product as well.

Reply With Quote
 
(login for full post details)
  #230 (permalink)
Griffith
Los Angeles, CA
 
 
Posts: 5 since Jun 2014
Thanks: 7 given, 1 received

Man, this looks cool, but I've spent a couple hours and I just cant get it to work.

After pressing "Download Risk Arrays" i'm just not getting anything in my destination folder.

Any ideas?

Reply With Quote
 
(login for full post details)
  #231 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received


Griffith View Post
Man, this looks cool, but I've spent a couple hours and I just cant get it to work.

After pressing "Download Risk Arrays" i'm just not getting anything in my destination folder.

Any ideas?


Did you double check to make sure the date is correct? It has to be in this format: 20140627

That's Friday. As far as I know, it won't pull in the closest business day, so you have to be precise.

If the download happens, you still won't see any data in Excel's "scanner" tab unless you select one or more (or all) commodities to scan.

You won't see any data in the "tracker" tab unless you've entered the data for one or more options in columns A-E. When you "track all" or "track select" it will then populate columns H-AD. Note: you can hide some of the columns in the H-AD range, if you don't use them.

Reply With Quote
 
(login for full post details)
  #232 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 957 given, 5,708 received

Anybody have an easy way to enter the Trade Dates on the Historic page? Column A. I have been hand typing them in.

Started this thread Reply With Quote
 
(login for full post details)
  #233 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
Anybody have an easy way to enter the Trade Dates on the Historic page? Column A. I have been hand typing them in.

This will list every pa2 file in your data folder in column A:

Sub ListPA2Files()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim ws As Worksheet

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set ws = Worksheets.Add
Sheets("Historic").Select

'Get the folder object associated with the directory
Set objFolder = objFSO.GetFolder("C:\Span4\Data\")
'ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"

'Loop through the Files collection
x = 5
For Each objFile In objFolder.Files
If InStr(objFile.Name, "cme") > 0 And InStr(objFile.Name, ".pa2") > 14 Then
x = x + 1
ws.Cells(x, 1).value = Mid(objFile.Name, 5, 8)
End If
Next

'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
End Sub


You could add something like this to the bottom of the download_risk_files routine to add the date every time you download a new pa2:

Range("Historic!A" & lastdate + 1) = CStr(Range("Scanner!G1"))

Hope this helps.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #234 (permalink)
 mu2pilot 
Dallas, TX
 
Experience: Advanced
Platform: T4, Zaner360, TOS
Broker: DeCarley Trading
Trading: Options
 
Posts: 104 since Sep 2013
Thanks: 134 given, 52 received


Dudetooth View Post
This will list every pa2 file in your data folder in column A:

Sub ListPA2Files()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim ws As Worksheet

Set objFSO = CreateObject("Scripting.FileSystemObject")
'Set ws = Worksheets.Add
Sheets("Historic").Select

'Get the folder object associated with the directory
Set objFolder = objFSO.GetFolder("C:\Span4\Data\")
'ws.Cells(1, 1).Value = "The files found in " & objFolder.Name & " are:"

'Loop through the Files collection
x = 5
For Each objFile In objFolder.Files
If InStr(objFile.Name, "cme") > 0 And InStr(objFile.Name, ".pa2") > 14 Then
x = x + 1
ws.Cells(x, 1).value = Mid(objFile.Name, 5, 8)
End If
Next

'Clean up!
Set objFolder = Nothing
Set objFile = Nothing
Set objFSO = Nothing
End Sub


You could add something like this to the bottom of the download_risk_files routine to add the date every time you download a new pa2:

Range("Historic!A" & lastdate + 1) = CStr(Range("Scanner!G1"))

Hope this helps.

Look at you and your tight object oriented code!

Reply With Quote
 
(login for full post details)
  #235 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

For the first time, the two daily files are not downloading and unzipping/extracting themselves. I thought it might be "my" version of PC-SPAN so I downloaded a fresh copy from the first page of this thread and the same thing happened.

Here's what theSPAN data folder looks like today. Note the CME file is not unzipped and it looks like the ICE file was not downloaded at all.

FWIW, I deleted the zipped CME file and tried again for 20140701 ... same result.

Attached Thumbnails
Click image for larger version

Name:	span.PNG
Views:	80
Size:	25.0 KB
ID:	150224  
Reply With Quote
 
(login for full post details)
  #236 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received


CafeGrande View Post
For the first time, the two daily files are not downloading and unzipping/extracting themselves. I thought it might be "my" version of PC-SPAN so I downloaded a fresh copy from the first page of this thread and the same thing happened.

Here's what theSPAN data folder looks like today. Note the CME file is not unzipped and it looks like the ICE file was not downloaded at all.

FWIW, I deleted the zipped CME file and tried again for 20140701 ... same result.

I had no problem today. Maybe just manually unzip the cme file, and grab the ice file from the ftp site, and manually unzip that too, and try again tomorrow.

Maybe you have a hard disk issue (hard disk out of space, for example)?

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #237 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

Well, whaddya know. A complete shut-down and reboot and now it works fine.

I'll leave the post up in case someone has a similar problem in the future.

The puzzling thing was the partial download over a couple of instances ... computers can make you crazy.

Reply With Quote
 
(login for full post details)
  #238 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received

CafeGrande,

I have been working on this in a number of different ways. I currently have the track sheet working in a category or group manner. See the screen shot below. The groups here a based different accounts, candidate selection and trade execution.

Considering the Ib risk naviagator I am thinking that such groups could be at a product level or at a product contract. For example; Soyabeans or Soyabeans July and Soyabeans August.

It occurs to me that you may wish to manage the greeks for different months differently. So to lump a trade you have in july with a trade you have in August would be unhelpful.

Thinking about how we can create a portfolio sheet with greeks and portfolio margin with profit/loss should not be too difficult but just getting the structure of the idea right first may be very helpful.

Any ideas or suggestions that you have would be appreciated.


Reply With Quote
 
(login for full post details)
  #239 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 957 given, 5,708 received


Dudetooth View Post
You could add something like this to the bottom of the download_risk_files routine to add the date every time you download a new pa2:

Range("Historic!A" & lastdate + 1) = CStr(Range("Scanner!G1"))

Hope this helps.

I added this but it doesn't work for me. Can you explain lastdate in that line? My column A is formatted as text. The dates are in the 20140702 format as text not dates.

Started this thread Reply With Quote
 
(login for full post details)
  #240 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


ron99 View Post
I added this but it doesn't work for me. Can you explain lastdate in that line?

Sorry, it should make more sense if you see it in the entire routine. Here's my sub for downloading the pa2 files ... the last block of code adds the new date if it doesn't already exist.

Sub download_risk_files()
On Error GoTo GetOut
Dim RetVal
Dim Batchfile As String
Dim path As String
Dim dateit As Double
Dim namecme As String, namenyb As String
Wait.Show vbModeless
DoEvents
dateit = Range("Scanner!G1").value
namecme = "cme." & dateit & ".s.pa2.zip"
namenyb = "nyb." & dateit & ".s.pa2.zip"
path = Range("Setup!A5").value '"C:\Span4\Data"

SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/cme/" & namecme, path & "\" & namecme
SaveWebFile "ftp://ftp.cmegroup.com/pub/span/data/nyb/" & namenyb, path & "\" & namenyb

Call UnZip(path & "\", path & "\" & namecme)
Call UnZip(path & "\", path & "\" & namenyb)
Kill path & "\" & namecme 'delete zipped risk arrays
Kill path & "\" & namenyb
Kill path & "\cme.s.pa2" '140215*** added update to cme/nyb file
Kill path & "\nyb.s.pa2"
FileCopy path & "\" & Left(namecme, Len(namecme) - 4), path & "\cme.s.pa2"
FileCopy path & "\" & Left(namenyb, Len(namenyb) - 4), path & "\nyb.s.pa2"

If Range("Setup!A18") = "Delete Dates" Then
If Dir(path & "\cme.s.pa2") <> "" Then 'delete old pa2 files if they exist
Kill path & "\cme.s.pa2" 'delete old pa2 files
End If
If Dir(path & "\nyb.s.pa2") <> "" Then 'delete old pa2 files if they exist
Kill path & "\nyb.s.pa2"
End If
Name path & "\" & "cme." & dateit & ".s.pa2" As path & "\cme.s.pa2"
Name path & "\" & "nyb." & dateit & ".s.pa2" As path & "\nyb.s.pa2"
End If

lastdate = Range("Historic!A" & Rows.Count).End(xlUp).Row '140222*** add dates to hist when downloading new pa2
Dim rng1 As Range
Dim strSearch As String
strSearch = dateit
Set rng1 = Range("A6:A" & lastdate).Find(strSearch, , xlValues, xlWhole)
If rng1 Is Nothing Then
Range("Historic!A" & lastdate + 1) = CStr(Range("Scanner!G1"))
End If

GetOut:
Unload Wait
End Sub

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #241 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received


BlueRoo View Post
CafeGrande,

Considering the Ib risk naviagator I am thinking that such groups could be at a product level or at a product contract. For example; Soyabeans or Soyabeans July and Soyabeans August.

It occurs to me that you may wish to manage the greeks for different months differently. So to lump a trade you have in july with a trade you have in August would be unhelpful.

Your work continues to impress!

And I agree with your comments above. I think the summary stats are most useful when presented by PRODUCT and then by month within that PRODUCT. The separation might not be that important in the financials or the metals, but as you know, futures prices and implied volatility can be very different across a three or four month range in the ags or energies.

In your working version, what happens if you have Sugar transactions across three brokerage accounts? Can you still summarize by Sugar, i.e. irrespective of brokerage account?

Reply With Quote
 
(login for full post details)
  #242 (permalink)
BlueRoo
Brisbane, Queensland, Australia
 
 
Posts: 121 since Aug 2013
Thanks: 75 given, 97 received


CafeGrande View Post
In your working version, what happens if you have Sugar transactions across three brokerage accounts? Can you still summarize by Sugar, i.e. irrespective of brokerage account?

Cafe,


Well essential you can have as many sections or headers as you like and have the same position listed in more than one section. It would be just an issue of managing it which is why thework in the interace and user interactivity. Make it a easy as possible to do complex things quickly. So you could have 3 brokerage accounts with all your trades and then headers by product, and on you could go...so one of the each things to do is to copy a trade and then past it multiple times in different locations. (in my file this is not a copy paste as you would do normally in excel...it is storing values to variables, reapplying them and then running trackselect or trackspread on each past refreshed.)

Reply With Quote
 
(login for full post details)
  #243 (permalink)
 eudamonia 
Sacramento, CA
 
Experience: None
Platform: None
Broker: ADM and Sierra Charts
Trading: ES, CL
 
eudamonia's Avatar
 
Posts: 315 since Jul 2010
Thanks: 308 given, 448 received

I'm just using the basic PC-SPAN program and for some reason I can no longer get it to calculate my margins. The data loads without an issue (files are the right size), but when I hit the calculate button nothing populates. Pretty sure it isn't the contract - I even tested front month regular ES futs to no avail. Tried rebooting the computer but no luck there.

Ever wonder why CME can't get out of 1991 and build some real software? Or perhaps just post their calcs on the website like ICE, LIFFE and everyone else

Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #244 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 957 given, 5,708 received


eudamonia View Post
I'm just using the basic PC-SPAN program and for some reason I can no longer get it to calculate my margins. The data loads without an issue (files are the right size), but when I hit the calculate button nothing populates. Pretty sure it isn't the contract - I even tested front month regular ES futs to no avail. Tried rebooting the computer but no luck there.

Ever wonder why CME can't get out of 1991 and build some real software? Or perhaps just post their calcs on the website like ICE, LIFFE and everyone else

Do you have any expired contracts you are trying to calculate?

I tried mine and it worked OK.

Sorry I wasn't much help.

Started this thread Reply With Quote
 
(login for full post details)
  #245 (permalink)
 eudamonia 
Sacramento, CA
 
Experience: None
Platform: None
Broker: ADM and Sierra Charts
Trading: ES, CL
 
eudamonia's Avatar
 
Posts: 315 since Jul 2010
Thanks: 308 given, 448 received


eudamonia View Post
I'm just using the basic PC-SPAN program and for some reason I can no longer get it to calculate my margins. The data loads without an issue (files are the right size), but when I hit the calculate button nothing populates. Pretty sure it isn't the contract - I even tested front month regular ES futs to no avail. Tried rebooting the computer but no luck there.

Ever wonder why CME can't get out of 1991 and build some real software? Or perhaps just post their calcs on the website like ICE, LIFFE and everyone else

Ok so the lesson is don't try to calculate PC-Span after a long week and you are mentally exhausted. The problem was completely user error

Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #246 (permalink)
 SMCJB 
Legendary Market Wizard
Houston, TX
 
Experience: Advanced
Platform: Trading Technologies
Broker: Primary Advantage Futures. Also ED&F and Tradestation
Trading: Primarily Energy but also a little GE, GC, SI & Bitcoin
 
Posts: 4,081 since Dec 2013
Thanks: 3,394 given, 8,081 received


eudamonia View Post
Ever wonder why CME can't get out of 1991 and build some real software? Or perhaps just post their calcs on the website like ICE, LIFFE and everyone else

Interestingly ICE have rewritten SPAN and now have their own version with is far easier to use and much more intuitive. It's also a lot easier to set up portfolio's and what if scenario's and to automate batch process's. The result files are also a lot easier to read.

Unfortunately it only works for ICE contracts though as it requires a new type of SPAN file (SP5 or SP6).

ICE SPAN Overview
ICE SPAN Download
ICE SPAN User Guide (PDF)
ICE SPAN Files

Reply With Quote
The following 3 users say Thank You to SMCJB for this post:
 
(login for full post details)
  #247 (permalink)
MGBRoadster
Lancashire UK
 
 
Posts: 52 since Jan 2013
Thanks: 42 given, 32 received

My Avast antivirus has an alert:

Object: C:\...\XLS-SPAN.xls
Infection: MO97: Downloader-DF
Process: C:Program Files (x86)Microsoft Office\Office14\EXCEL.EXE

I've been using XLS-SPAN for several months without any problems, so I guess this is a false positive. Does anybody know what's happening here?


Thanks,

Chris

Reply With Quote
 
(login for full post details)
  #248 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 957 given, 5,708 received

I use Avast and have never had that alert.

Started this thread Reply With Quote
 
(login for full post details)
  #249 (permalink)
 Physicsman 
London + Great Britain
 
Experience: Master
Platform: Interactive Brokers, OptionsXpress, TradeMonster, RJO, AmiBroker, Wealthlab
Trading: Futures, Options, Equities
 
Physicsman's Avatar
 
Posts: 19 since May 2013
Thanks: 85 given, 25 received

@MGBRoadster

I had the same message over the weekend. The only way I got Avast to stop auto-deleting the Excel file was to put it in the exclusions list for the FileShield.

You can only put directory paths rather than a single file in the exclusions list. I reported it as a false positive to Avast, but so far their updates haven't taken that into account yet. Hopefully, it will resolve with some further definition updates from Avast.

Reply With Quote
The following 2 users say Thank You to Physicsman for this post:
 
(login for full post details)
  #250 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received


MGBRoadster View Post
My Avast antivirus has an alert:

Object: C:\...\XLS-SPAN.xls
Infection: MO97: Downloader-DF
Process: C:Program Files (x86)Microsoft Office\Office14\EXCEL.EXE

I've been using XLS-SPAN for several months without any problems, so I guess this is a false positive. Does anybody know what's happening here?


Thanks,

Chris

I've gotten it too. Must be an updated vrius database for Avast, and a new virus looks like the SPAN downloader code...

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #251 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


MGBRoadster View Post
My Avast antivirus has an alert:

Object: C:\...\XLS-SPAN.xls
Infection: MO97: Downloader-DF
Process: C:Program Files (x86)Microsoft Office\Office14\EXCEL.EXE

I've been using XLS-SPAN for several months without any problems, so I guess this is a false positive. Does anybody know what's happening here?


Thanks,

Chris

I came across this over the weekend as well. I tracked it down to the SaveWebFile function ... I think that the process it uses ("MSXML2.XMLHTTP") is causing the hit for the anti-virus software. I submitted a copy to a couple of sites to have it checked just to be sure.

In the mean time, you can get the spreadsheet operational again by doing the following:
-Delete the SaveWebFile function, should be in the Functions module
-Add the following code to the top of the Funtion module:

Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Private Function SaveWebFile (URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then DownloadFile = True
End Function


This method of downloading the PA2 file does not seem to cause any grief with the anti-virus software and should be all the editing that is needed (not sure which version of the spreadsheet you have). If it doesn't work, just let me know which version you are using and I should be able to get it fixed up for you.

Hope this helps.

Note: If Avast keeps auto-deleting your file (placing it in the virus chest) you can turn Avast off temporarily, restore the spreadsheet, edit the code, and then turn Avast back on.

Update...

Sorry, small mistake ... code should look like this:

Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Private Function SaveWebFile (URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then SaveWebFile = True
End Function

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #252 (permalink)
 Big Mike 
Site Administrator
Swing Trader
Data Scientist & DevOps
Manta, Ecuador
 
Experience: Advanced
Platform: Custom solution
Trading: Futures & Crypto
 
Big Mike's Avatar
 
Posts: 50,066 since Jun 2009
Thanks: 32,529 given, 98,481 received

Use the site:

https://www.virustotal.com/

And it will test the file against a bunch of engines, and you can make an informed decision about false positives.

Mike

We're here to help -- just ask

For the best trading education, watch our webinars
Searching for trading reviews? Review this list

Follow us on Twitter, YouTube, and Facebook

Support our community as an Elite Member:
https://futures.io/elite/

Visit other sites? Please spread the word about your experience with our community!
Follow me on Twitter Visit my futures io Trade Journal Reply With Quote
 
(login for full post details)
  #253 (permalink)
MGBRoadster
Lancashire UK
 
 
Posts: 52 since Jan 2013
Thanks: 42 given, 32 received


Dudetooth View Post
In the mean time, you can get the spreadsheet operational again by doing the following:
-Delete the SaveWebFile function, should be in the Functions module
-Add the following code to the top of the Funtion module:

... code should look like this:

Private Declare Function URLDownloadToFileA Lib "urlmon" (ByVal pCaller As Long, _
ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, _
ByVal lpfnCB As Long) As Long

Private Function SaveWebFile (URL As String, LocalFilename As String) As Boolean
Dim lngRetVal As Long
lngRetVal = URLDownloadToFileA(0, URL, LocalFilename, 0, 0)
If lngRetVal = 0 Then SaveWebFile = True
End Function

Sorry Dudetooth - old guy here, I've never used Visual basic before

I've gone to the Functions page and deleted the section of text that contains SaveWebfile, and added your new code at the top of the page. I get a
"Compile error: Only comments may appear after the End Sub, End Function or End Property"

and the following text is highlighted:
" Private Declare Function GetWindowLong _
Lib "user32.dll" _
Alias "GetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long) As Long "

I'm not sure how to find out which version of the spreadsheet I have, so I tried downloading the latest version from the first page of this thread. I had the same issue again, but when I try to download it now I get a "The file is corrupt and cannot be opened" error.

I'm probably making an obvious mistake.

Reply With Quote
 
(login for full post details)
  #254 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


MGBRoadster View Post
Sorry Dudetooth - old guy here, I've never used Visual basic before.

I'll get a patched version of the spreadsheet out for you.

Reply With Quote
 
(login for full post details)
  #255 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

Here is a patched version of the last XLS-SPAN that uses a different download method that is not throwing false positives with the anti-virus.

I did check the code with the VirusTotal that Big Mike suggested. Off 53 anti-virus programs, the code caused hits on four, including Avast. After the code used to save web files was switched out with the code used in the attached spreadsheet only one anti-virus still showed a hit ... TheHacker. From a quick search online it seems as though TheHacker has a bit of a history with false positives.

I also submitted the file to Avast. They recommended that I try updating my version of Avast and virus definitions to alleviate the false positive.

Just wanted to share what I found.

Attached Files
Register to download File Type: xls XLS-SPAN (04b) - Update.xls (673.0 KB, 44 views)
Reply With Quote
The following 4 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #256 (permalink)
Mo111
Detroit MI/USA
 
 
Posts: 21 since Jan 2014
Thanks: 12 given, 10 received


Dudetooth View Post
Here is a patched version of the last XLS-SPAN that uses a different download method that is not throwing false positives with the anti-virus.

I did check the code with the VirusTotal that Big Mike suggested. Off 53 anti-virus programs, the code caused hits on four, including Avast. After the code used to save web files was switched out with the code used in the attached spreadsheet only one anti-virus still showed a hit ... TheHacker. From a quick search online it seems as though TheHacker has a bit of a history with false positives.

I also submitted the file to Avast. They recommended that I try updating my version of Avast and virus definitions to alleviate the false positive.

Just wanted to share what I found.

Hi Dudetooth,

I've been using your excel sheet here - and decided to move over to the 04b excel sheet as the "track all" feature would save me a lot of time with each of my spreads/strangles.

I'm getting different calculations for margin though between the two excel files - heres an example of my CL strangle:

version 3 sheet - $86 margin:



version 4b - $53 margin:



All of the settings on the first sheet are the same: path, Total IM, commissions, save dates. Also, the scan date is the same 8/29/2014.

Reply With Quote
 
(login for full post details)
  #257 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


Mo111 View Post
I'm getting different calculations for margin though between the two excel files

Mo111,

Thanks for finding this error ... the 4b version was not adding the short option minimum for both legs ... $86 is correct. You won't normally see this unless you are very far out of the money (which these are) and then the short option minimum is greater than the risk scenarios so that it becomes the basis of the initial margin. I've fixed this issue and have re-posted the spreadsheet.

Attached Files
Register to download File Type: xls XLS-SPAN (04b) - Update.xls (652.5 KB, 229 views)
Reply With Quote
The following 6 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #258 (permalink)
cubcub
Boston MA
 
 
Posts: 5 since Apr 2014
Thanks: 1 given, 0 received

Hello everyone,

I use win8 64 bit. I have downloaded pc-span 4.5 from CME yet can't install it. The message says unexpected error with the package?? error code 2502 and 2503.


Anyone could help? Thank you!!!

Attached Thumbnails
Click image for larger version

Name:	2502.JPG
Views:	64
Size:	18.4 KB
ID:	154736   Click image for larger version

Name:	2503.JPG
Views:	69
Size:	18.3 KB
ID:	154737  
Reply With Quote
 
(login for full post details)
  #259 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


cubcub View Post
Hello everyone,

I use win8 64 bit. I have downloaded pc-span 4.5 from CME yet can't install it. The message says unexpected error with the package?? error code 2502 and 2503.


Anyone could help? Thank you!!!


There is no issues with Win7 64bit, but it could be Win8 security ... found this referring to error 2502 and 2503:

Windows 8 has increased security measures which prevent standard users from installing 3rd party applications. If you see error 2502 or 2503 while installing, that means your account does not have administrator permissions to install software.

Solution 1:

Make sure your Windows account has the highest level of security permissions. Refer to Windows documentation for your operating system on how to do this.

Solution 2:

Run the installation file "As Administrator":

1. Find the installation file on your computer. Normally you have the option to save or run an installation file when downloading from the Internet. If you do not know where the file was downloaded, check the Downloads folder in File Explorer.

2. Right-click the file and select Run As Administrator* from the pop-up menu.

Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #260 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

DudeTooth -

I have a selfish request unrelated to the issue immediately above.

Would it take a lot of work to add the Kansas City (KE) wheat contract to your application? It doesn't have the liquidity of the Chicago contract, but now that they're both under the CME umbrella, that may change.

No rush ... I can keep tabs on my KE positions elsewhere, but if it fits into a future release, great!

Reply With Quote
 
(login for full post details)
  #261 (permalink)
Mo111
Detroit MI/USA
 
 
Posts: 21 since Jan 2014
Thanks: 12 given, 10 received


Dudetooth View Post
Mo111,

Thanks for finding this error ... the 4b version was not adding the short option minimum for both legs ... $86 is correct. You won't normally see this unless you are very far out of the money (which these are) and then the short option minimum is greater than the risk scenarios so that it becomes the basis of the initial margin. I've fixed this issue and have re-posted the spreadsheet.

Dudetooth, Thanks for the updated excel sheet - Now im running into a different error. When I try to download the current days files, I get a "sub function not defined":



Does this have to do with the updated virus definitions/scanner?

Reply With Quote
 
(login for full post details)
  #262 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


Mo111 View Post
Does this have to do with the updated virus definitions/scanner?

I doubt it. The last version I posted had the fix for the virus scanners (uses a different routine to download files). I just tried to download the PA2 files using that spreadsheet and received no errors. I suspect that your pc had a hiccup. Try a reboot and then try the download again. Let me know if that doesn't fix the issue.

Reply With Quote
 
(login for full post details)
  #263 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


CafeGrande View Post
Would it take a lot of work to add the Kansas City (KE) wheat contract to your application?

CafeGrande,

Not much at all.

Add these lines of code to the Function P34Bstring:

Case "KW", "KE"
strS(0) = "P CBTKW OOF"
strS(1) = "3 KCW 1001"
strS(2) = "4 KCW 01"
strS(3) = "B CBTKW OOF"
strS(4) = "82CBTKW KW FUT"


And add these lines of code to the Function T8string:

Case "KW", "KE"
str5 = "82CBTKW KW OOF"


I'm not sure if the spacing as displayed are correct, so I attached a text file with the code for the two functions.

I gave it a quick test and it seemed to work ... let me know if there are any issues.

Attached Files
Register to download File Type: txt Function Code.txt (10.3 KB, 9 views)
Reply With Quote
 
(login for full post details)
  #264 (permalink)
cubcub
Boston MA
 
 
Posts: 5 since Apr 2014
Thanks: 1 given, 0 received

Thank you, it worked.


Dudetooth View Post
There is no issues with Win7 64bit, but it could be Win8 security ... found this referring to error 2502 and 2503:

Windows 8 has increased security measures which prevent standard users from installing 3rd party applications. If you see error 2502 or 2503 while installing, that means your account does not have administrator permissions to install software.

Solution 1:

Make sure your Windows account has the highest level of security permissions. Refer to Windows documentation for your operating system on how to do this.

Solution 2:

Run the installation file "As Administrator":

1. Find the installation file on your computer. Normally you have the option to save or run an installation file when downloading from the Internet. If you do not know where the file was downloaded, check the Downloads folder in File Explorer.

2. Right-click the file and select Run As Administrator* from the pop-up menu.


Reply With Quote
 
(login for full post details)
  #265 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received


Dudetooth View Post
CafeGrande,

Add these lines of code to the Function P34Bstring:

....

I'm not sure if the spacing as displayed are correct, so I attached a text file with the code for the two functions.

I gave it a quick test and it seemed to work ... let me know if there are any issues.


It worked! I didn't break anything!

Thanks for adding the .txt file. That helped this novice to know what to look for.

Reply With Quote
 
(login for full post details)
  #266 (permalink)
 daydayup8 
tennessee/USA
 
Experience: Intermediate
Platform: Quotetracker, TOS
Broker: OX TOS
Trading: ES
 
Posts: 83 since Aug 2014
Thanks: 267 given, 8 received

I didn't find it in the quick summary link, thanks!
Sorry to bother you, I reviewed a few pages of this thread and did not find the link to buy the program.
it looks like the real challenge for me now is to figure out how to use the SPAN:-(

Edit1, I found the link!
Edit2: This link does not show the price for $10.00, it is risk manager for $3000.00, I still need help.

https://www.cmegroup.com/clearing/risk-management/span-purchase.html



ron99 View Post
We will use this thread to discuss working with the CME PC-SPAN program.


Reply With Quote
 
(login for full post details)
  #267 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 957 given, 5,708 received

It looks like CME finally redid PC-SPAN and now calls it CME CORE (Clearing Online Risk Engine). It's free.

https://www.cmegroup.com/clearing/cme-core-cme-clearing-online-risk-engine.html

I haven't looked at it in detail. Just watched some of the videos. Someone needs to check it out in detail.

Started this thread Reply With Quote
The following 2 users say Thank You to ron99 for this post:
 
(login for full post details)
  #268 (permalink)
Mo111
Detroit MI/USA
 
 
Posts: 21 since Jan 2014
Thanks: 12 given, 10 received


Dudetooth View Post
I doubt it. The last version I posted had the fix for the virus scanners (uses a different routine to download files). I just tried to download the PA2 files using that spreadsheet and received no errors. I suspect that your pc had a hiccup. Try a reboot and then try the download again. Let me know if that doesn't fix the issue.

Hi Dudetooth - thanks for all your help. So I figured out what the issue was. I was getting the same error as before for "64-bit code" and I did your modification you mentioned here, which got rid of the error but then it deleted the download function for the spa files. Anyways a quick google search brought me here, and all I did was add the word "PtrSafe" before the declare statements and it worked great.

The previous error with margin calculation of the CL contracts is fixed! Thanks

Now I'm facing two other issues with the new sheet. First, I'm getting a different SPAN margin calculation for my 6E contracts from the prior version: (scan date for both is 9/3/2014)

4b version says $455:



prior version says $461:





The second issue is I'm using the tracking page to track trades in three separate accounts - which have the same contracts but different quantities. When I use the track all button, it states the same position IM for ALL of them (its only correct for with the account with the most contracts). Example below where the Position IM is the same for all three - when its only correct for only 21 contracts:





Thank you in advance for your help!

Reply With Quote
 
(login for full post details)
  #269 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


Mo111 View Post
Now I'm facing two other issues with the new sheet.

Mo111,

With your first issue:
$461 per spread is the correct amount. If I remember correctly (and not get too far in the weeds), the reason for the difference in price is that when I made the XLSv3 I did not have it coded properly for the cabinet value. The XLSv4b is grabbing the cabinet value as it is defined in the PA2 file. Since you are using Total Margin the value of the options are added in to the initial margin.

For your second issue:
That is a known issue when running Track All, but I believe I have a work-around for you.
-Replace the TrackAll code in your spreadsheet with the code from the attached text file.
-For any position that you have multiple lines with the same option month and same strike, give it a unique spread number in column F, then when the code is run it will treat it separately and you should see the different results.


Attached Files
Register to download File Type: txt TrackAll.txt (1.3 KB, 9 views)
Reply With Quote
 
(login for full post details)
  #270 (permalink)
Mo111
Detroit MI/USA
 
 
Posts: 21 since Jan 2014
Thanks: 12 given, 10 received


Dudetooth View Post
Mo111,

With your first issue:
$461 per spread is the correct amount.

The Track all code worked great!! Thank you! This is going to save me lots of time!

As for the margin for that spread did you mean to say $455 is the correct amount? I got $455 with v4b and $461 with v3.

Reply With Quote
 
(login for full post details)
  #271 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


Mo111 View Post
The Track all code worked great!! Thank you! This is going to save me lots of time!

As for the margin for that spread did you mean to say $455 is the correct amount? I got $455 with v4b and $461 with v3.

I apologize. I almost have to re-learn my old code when I go back through it and I got that one messed up.

The issue was in the option value function. It was fine with v3 and v4b was off, but it wasn't just as simple as cut and paste from one to the other (the structure changed), so replace the function optionvalue with the code in the attached text file.

I gave it a quick check and it seemed to work, but let me know if you find any other issues.

Attached Files
Register to download File Type: txt optionvalue.txt (977 Bytes, 19 views)
Reply With Quote
 
(login for full post details)
  #272 (permalink)
 kevinkdog   is a Vendor
 
 
Posts: 3,002 since Jul 2012
Thanks: 1,598 given, 5,963 received

Is anyone else having a problem with 10/3 data from CME in @Dudetooth 's spreadsheet? I can get ICE data to work, but not 10/3 CME data.

Follow me on Twitter Reply With Quote
 
(login for full post details)
  #273 (permalink)
 ron99 
Market Wizard
Cleveland, OH
 
Experience: Advanced
Platform: QST
Broker: QST, DeCarley Trading, Gain
Trading: Options on Futures
 
Posts: 3,059 since Jul 2011
Thanks: 957 given, 5,708 received


kevinkdog View Post
Is anyone else having a problem with 10/3 data from CME in @Dudetooth 's spreadsheet? I can get ICE data to work, but not 10/3 CME data.

The CME file I downloaded for 10/3 works OK in PC-SPAN but I am getting incorrect IMs in Dudetooth's spreadsheet.

Started this thread Reply With Quote
The following user says Thank You to ron99 for this post:
 
(login for full post details)
  #274 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received


kevinkdog View Post
Is anyone else having a problem with 10/3 data from CME in @Dudetooth 's spreadsheet? I can get ICE data to work, but not 10/3 CME data.

Same thing. The two files download OK and if I "track select" or "scan select" ICE contracts the application works fine. If I include CME contracts the "calculating - please wait" window displays for a long time and eventually Excel will freeze ("not responding") and must be force closed. The Excel detail window called it "AppHangB1"

Reply With Quote
The following user says Thank You to CafeGrande for this post:
 
(login for full post details)
  #275 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


kevinkdog View Post
Is anyone else having a problem with 10/3 data from CME in @Dudetooth 's spreadsheet? I can get ICE data to work, but not 10/3 CME data.

I don't know if the data is messed up in the zipped file, but it seems to be missing the carriage-return character at the end of the lines (if you open it in notepad, all the data is run together as one continuous line). PC-SPAN must be coded to read the data even without the carriage-return character, but my code looks to read each line.

https://ftp://ftp.cmegroup.com/pub/span/data/cme/cme.s.pa2

Sorry about this, but downloading the cme.s.pa2 has the same issue. What I originally did was open the link in my browser where it was displayed with the line breaks. I copied that and pasted it into notepad and saved it as my pa2. I assumed that downloaded the cme.s.pa2 would work and just realized now that it had the same issue.

Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #276 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

^^ The revised method worked for me.

- let the whole thing download in a browser window
- CTRL A, CTRL C, open a notepad, CTRL V
- save notepad in SPAN4 > DATA folder, make sure it's saved as "all files" and the correct file extension (.pa2) will magically appear (the default save mode might be .txt, which won't work).

Thanks, DudeTooth!

Reply With Quote
The following user says Thank You to CafeGrande for this post:
 
(login for full post details)
  #277 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received

A little more clarification on the issue with Friday's CME data.

Normally, the pa2 files have a Windows encoding for line breaks ... the characters CrLf:


Friday's data appears to have had a Unix encoding, using only Lf to designate the line breaks:


PC-SPAN is apparently fine with just Lf, but VBA needs to see the CrLf. This issue appears to have been a big enough of a deal to prompt CME to replace all of Friday's pa2 files with updated ones that have the CrLf encoding in place. This should indicate that we're not likely to see this happen again, but if it does the following code can fix it for you.

In the Functions module add this routine:

Sub FixLf(FileName As String) 'code to replace vbLf with vbCrLf if CME's pa2 files switch to unix code
Dim buff
On Error Resume Next
Open FileName For Input As #1
buff = Input$(LOF(1), #1)
Close #1

If InStr(buff, vbCrLf) = 0 Then
buff = Replace$(buff, vbLf, vbCrLf)
End If

Open FileName For Output As #1
Write #1, buff
Close #1
On Error GoTo 0
End Sub


In the Sub download_risk_files() add these lines right before the end of the sub where you see the line "GetOut:" so your last 5 lines look like this:

Call FixLf(path & "\cme." & dateit & ".s.pa2")
Call FixLf(path & "\nyb." & dateit & ".s.pa2")
GetOut:
Unload Wait
End Sub


You can load this code now if you want. It will check your downloaded pa2 for CrLf encoding. If it is present it leaves things alone, but if it can't find any CrLf it will assume that Lf is being used and change all Lf encoding to CrLf.

Reply With Quote
The following 5 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #278 (permalink)
 daydayup8 
tennessee/USA
 
Experience: Intermediate
Platform: Quotetracker, TOS
Broker: OX TOS
Trading: ES
 
Posts: 83 since Aug 2014
Thanks: 267 given, 8 received

I am trying to figure out how to use Dudetooth's program to calculate margin without SPAN program installed, when I click the link on the summary page to get Risk array: it is something like this, there is no pa2 anywhere. What I have done:
1: downloaded his latest program: XLS-SPAN(04b)
2: Built a path C:\SPAN4\DATA on my computer

Please help if anyone knows where is the pa2 file, thanks!




Index of /pub/span/data/
Name Size Date Modified
[parent directory]
asxcl/ 10/24/14, 3:35:00 AM
asxclf/ 10/24/14, 1:35:00 AM
bfx/ 10/24/14, 2:05:00 PM
bmdc/ 10/24/14, 6:25:00 AM
bourseafr/ 10/24/14, 1:34:00 PM
bse/ 5/17/14, 12:36:00 PM
cbt/ 8/17/09, 12:00:00 AM
cce/ 4/27/12, 12:00:00 AM
ccl/ 4/27/12, 12:00:00 AM
ccx/ 12/26/13, 12:00:00 AM
cdc/ 10/23/14, 5:14:00 PM
cee/ 10/24/14, 9:31:00 AM
cfe/ 10/23/14, 6:12:00 PM
clearing/ 1/2/07, 12:00:00 AM
cme/ 10/24/14, 11:33:00 AM
cmx/ 4/27/12, 12:00:00 AM
dgc/ 10/24/14, 7:21:00 AM
difx/ 10/23/14, 6:54:00 AM
ecc/ 10/24/14, 12:45:00 PM
elx/ 10/23/14, 6:17:00 PM
eus/ 9/24/10, 12:00:00 AM
hkf/ 10/24/14, 9:08:00 AM
ice/ 10/23/14, 6:21:00 PM
jcch/ 10/24/14, 2:44:00 AM
jsc/ 10/24/14, 1:54:00 AM
kcb/ 12/26/13, 12:00:00 AM
kdpw/ 10/24/14, 12:17:00 PM
kel/ 10/22/14, 11:44:00 AM
lch/ 5/15/14, 6:01:00 AM
liffe/ 10/24/14, 1:55:00 PM
lme/ 10/23/14, 3:52:00 PM
mat/ 10/24/14, 12:40:00 PM
mcx/ 10/24/14, 2:14:00 PM
mcx-sx/ 9/15/14, 11:03:00 PM
mge/ 10/23/14, 4:04:00 PM
micex/ 12/26/13, 12:00:00 AM
nocc/ 12/26/13, 12:00:00 AM
nos/ 5/15/14, 6:01:00 AM
nse/ 10/20/14, 1:21:00 AM
nyb/ 10/23/14, 5:15:00 PM
nyl/ 10/23/14, 5:34:00 PM
nym/ 4/27/12, 12:00:00 AM
nzf/ 12/26/13, 12:00:00 AM
nzx/ 10/24/14, 12:14:00 AM
ocx/ 10/23/14, 6:17:00 PM
ose/ 12/26/13, 12:00:00 AM
pbt/ 2/15/14, 12:00:00 AM
pnx/ 4/27/12, 12:00:00 AM
sdco/ 10/24/14, 7:49:00 AM
sfe/ 12/26/13, 12:00:00 AM
smx/ 10/24/14, 1:35:00 PM
smxcc/ 3/15/14, 12:00:00 AM
taifex/ 10/24/14, 1:55:00 AM
takas/ 10/24/14, 10:15:00 AM
tcc/ 2/12/14, 12:00:00 AM
test_exch/ 10/28/13, 12:00:00 AM
tif/ 10/24/14, 2:04:00 AM
tse/ 3/15/13, 12:00:00 AM
wce/ 10/23/14, 4:05:00 PM
xeu/ 9/24/10, 12:00:00 AM
xma/ 10/23/14, 8:49:00 PM
xny/ 10/23/14, 6:17:00 PM

Reply With Quote
 
(login for full post details)
  #279 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


daydayup8 View Post
I am trying to figure out how to use Dudetooth's program to calculate margin without SPAN program installed, when I click the link on the summary page to get Risk array: it is something like this, there is no pa2 anywhere. What I have done:
1: downloaded his latest program: XLS-SPAN(04b)
2: Built a path C:\SPAN4\DATA on my computer

Please help if anyone knows where is the pa2 file, thanks!

daydayup8,

Did you grab the XLS-SPAN from post #2? I lost track, but I know that I did get the latest patched file linked on post #2.

Did you go through the instructions on the Setup tab? Section 2 covers downloading the pa2 files. All you need to do is enter the date (YYYYMMDD format, like 20141024) in cell G1 on the Scanner tab and then click the Download Risk button. It goes out and finds the right web address and downloads the pa2 files to your C:\SPAN4\DATA folder.

Once that is done then you can scan for trades or enter positions on the Tracker tab to follow their progress from day to day. I think that the Setup tab instructions cover all of that and there should be some example positions on the Tracker tab to show you how to set them up.

I hope this helps ... if not, just let me know.

Reply With Quote
 
(login for full post details)
  #280 (permalink)
 daydayup8 
tennessee/USA
 
Experience: Intermediate
Platform: Quotetracker, TOS
Broker: OX TOS
Trading: ES
 
Posts: 83 since Aug 2014
Thanks: 267 given, 8 received

Yes, I printed out the Setup tab and did as told step by step, but nothing came out except something like program flashing/shining then disappeared from my screen, it was fast and I could see what was that.
I am not good at computers/codes, I've finished reading this whole thread and tried to understand what you guys were talking about, most of posts are pretty hard to understand. Luckily and thanks to your hard work, there is a program to use without installing PC SPAN, but I just could use it. Frustrated...


Dudetooth View Post
daydayup8,

Did you grab the XLS-SPAN from post #2? I lost track, but I know that I did get the latest patched file linked on post #2.

Did you go through the instructions on the Setup tab? Section 2 covers downloading the pa2 files. All you need to do is enter the date (YYYYMMDD format, like 20141024) in cell G1 on the Scanner tab and then click the Download Risk button. It goes out and finds the right web address and downloads the pa2 files to your C:\SPAN4\DATA folder.

Once that is done then you can scan for trades or enter positions on the Tracker tab to follow their progress from day to day. I think that the Setup tab instructions cover all of that and there should be some example positions on the Tracker tab to show you how to set them up.

I hope this helps ... if not, just let me know.


Reply With Quote
 
(login for full post details)
  #281 (permalink)
 daydayup8 
tennessee/USA
 
Experience: Intermediate
Platform: Quotetracker, TOS
Broker: OX TOS
Trading: ES
 
Posts: 83 since Aug 2014
Thanks: 267 given, 8 received

First, i put '10232014' on cell G, then i went to 'Set up' tab to click 'Save Dates', then I went back to 'Scanner' tab to clink 'Download Risk', then something like this pop out 'Calculating, please wait', it takes a few seconds, then disappeared.
Next step: After enter for column A-F, I clicked 'Scan All' button, something flashed for a second on my screen but nothing happened then.


Dudetooth View Post
daydayup8,

Did you grab the XLS-SPAN from post #2? I lost track, but I know that I did get the latest patched file linked on post #2.

Did you go through the instructions on the Setup tab? Section 2 covers downloading the pa2 files. All you need to do is enter the date (YYYYMMDD format, like 20141024) in cell G1 on the Scanner tab and then click the Download Risk button. It goes out and finds the right web address and downloads the pa2 files to your C:\SPAN4\DATA folder.

Once that is done then you can scan for trades or enter positions on the Tracker tab to follow their progress from day to day. I think that the Setup tab instructions cover all of that and there should be some example positions on the Tracker tab to show you how to set them up.

I hope this helps ... if not, just let me know.


Reply With Quote
 
(login for full post details)
  #282 (permalink)
 daydayup8 
tennessee/USA
 
Experience: Intermediate
Platform: Quotetracker, TOS
Broker: OX TOS
Trading: ES
 
Posts: 83 since Aug 2014
Thanks: 267 given, 8 received

I did the whole process again and I checked the path 'C:\SPAN4\Data', I did not see any folder or data in this path.


Dudetooth View Post
All you need to do is enter the date (YYYYMMDD format, like 20141024) in cell G1 on the Scanner tab and then click the Download Risk button. It goes out and finds the right web address and downloads the pa2 files to your C:\SPAN4\DATA folder.


Reply With Quote
 
(login for full post details)
  #283 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


daydayup8 View Post
Frustrated...

I can try to troubleshoot things with you if you want. Shoot me a private message and we can go from there.

Reply With Quote
 
(login for full post details)
  #284 (permalink)
 Physicsman 
London + Great Britain
 
Experience: Master
Platform: Interactive Brokers, OptionsXpress, TradeMonster, RJO, AmiBroker, Wealthlab
Trading: Futures, Options, Equities
 
Physicsman's Avatar
 
Posts: 19 since May 2013
Thanks: 85 given, 25 received


daydayup8 View Post
First, i put '10232014' on cell G, <snip>

@daydayup8 I believe you have to put the date in the sheet exactly in the same format as the template - so it should be 20141023. @Dudetooth can correct me if I'm wrong. As far as I can see the date template is year, month, day in the format: YYYYMMDD.

Reply With Quote
 
(login for full post details)
  #285 (permalink)
 daydayup8 
tennessee/USA
 
Experience: Intermediate
Platform: Quotetracker, TOS
Broker: OX TOS
Trading: ES
 
Posts: 83 since Aug 2014
Thanks: 267 given, 8 received

sorry, I posed it wrong, but I actually typed '20141023' since Dudetooth had '20140829' there, I just modified his date.


Physicsman View Post
@daydayup8 I believe you have to put the date in the sheet exactly in the same format as the template - so it should be 20141023. @Dudetooth can correct me if I'm wrong. As far as I can see the date template is year, month, day in the format: YYYYMMDD.


Reply With Quote
 
(login for full post details)
  #286 (permalink)
 daydayup8 
tennessee/USA
 
Experience: Intermediate
Platform: Quotetracker, TOS
Broker: OX TOS
Trading: ES
 
Posts: 83 since Aug 2014
Thanks: 267 given, 8 received

There is no data in the 'C:\SPAN4\DATA' path after I put '20141023' and then click 'Download Risk'.

Attached Thumbnails
Click image for larger version

Name:	SPAN_empty data.png
Views:	80
Size:	182.0 KB
ID:	163335  
Reply With Quote
 
(login for full post details)
  #287 (permalink)
 daydayup8 
tennessee/USA
 
Experience: Intermediate
Platform: Quotetracker, TOS
Broker: OX TOS
Trading: ES
 
Posts: 83 since Aug 2014
Thanks: 267 given, 8 received

Thank you Dudetooth for your time and help, your program is so useful and amazing! I am fascinated by it when it is just running in front of my eyes, beautiful!!

I am confident that I can make some good trades from now on:-)

Nothing can express my appreciation, wish you a blessed day!

Cheers!

Reply With Quote
The following user says Thank You to daydayup8 for this post:
 
(login for full post details)
  #288 (permalink)
CafeGrande
St Paul, MN, USA
 
 
Posts: 200 since Jan 2014
Thanks: 130 given, 207 received

Hi - this is a (minor) caution about using the ATM IV figures in the CME SPAN files for long term tables and charts.

Below I've attached an image that compares CME SPAN IV on nat gas with the IV from three other sources.

Good news: there is very little variation across sources once you go out three or four months.
Bad news: the SPAN model seems to understate ATM IV in the nearby months.

This is not exhaustive research by any means, but CME ATM IV is also understated in grains (I noticed the issue a couple of weeks ago) and silver and probably other products. I don't know what causes it to be off in the nearby months and spot-on in the deferredes (it's not the interest rate input).

If you want precise historical data, you will probably need to get it elsewhere. If you just want to know the general range then the SPAN data is good enough for government work.

Attached Thumbnails
Click image for larger version

Name:	option models.PNG
Views:	93
Size:	24.5 KB
ID:	165125  
Reply With Quote
 
(login for full post details)
  #289 (permalink)
Textrader
Atlanta
 
 
Posts: 3 since Sep 2014
Thanks: 0 given, 3 received


Dudetooth View Post
A minor addition seems to be the source of the problem.


In the Functions Module delete:

'Written: October 07, 2007
'Author: Leith Ross
'Summary: Add Minimize, and Maximize/Restore buttons to a VBA UserForm

Private Const GWL_STYLE As Long = -16
Public Const MIN_BOX As Long = &H20000
Public Const MAX_BOX As Long = &H10000

Const SC_CLOSE As Long = &HF060
Const SC_MAXIMIZE As Long = &HF030
Const SC_MINIMIZE As Long = &HF020
Const SC_RESTORE As Long = &HF120

Private Declare Function GetWindowLong _
Lib "user32.dll" _
Alias "GetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong _
Lib "user32.dll" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal dwNewLong As Long) As Long

'Redraw the Icons on the Window's Title Bar
Private Declare Function DrawMenuBar _
Lib "user32.dll" _
(ByVal hwnd As Long) As Long

'Returns the Window Handle of the Window accepting input
Private Declare Function GetForegroundWindow _
Lib "user32.dll" () As Long

Public Sub AddToForm(ByVal Box_Type As Long)

Dim BitMask As Long
Dim Window_Handle As Long
Dim WindowStyle As Long
Dim Ret As Long

If Box_Type = MIN_BOX Or Box_Type = MAX_BOX Then
Window_Handle = GetForegroundWindow()

WindowStyle = GetWindowLong(Window_Handle, GWL_STYLE)
BitMask = WindowStyle Or Box_Type

Ret = SetWindowLong(Window_Handle, GWL_STYLE, BitMask)
Ret = DrawMenuBar(Window_Handle)
End If

End Sub


View the code of the Help form and delete:

Private Sub UserForm_Activate()
AddToForm MIN_BOX
End Sub





Dudetooth: I have tried several times to modify the code as suggested above on my Windows 8.1 - 64 bit laptop and continue to get the same error message. I am not sure if the 8.1 update affected the code, any suggestions? Thanks, Chris.

Reply With Quote
 
(login for full post details)
  #290 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


Textrader View Post
Dudetooth: I have tried several times to modify the code as suggested above on my Windows 8.1 - 64 bit laptop and continue to get the same error message. I am not sure if the 8.1 update affected the code, any suggestions? Thanks, Chris.

Chris,

Try this version. If you still get errors just post the details and I'll see if I can work it out for you.

Attached Files
Register to download File Type: xls XLS-SPAN (04b3) - Update.xls (587.0 KB, 62 views)
Reply With Quote
 
(login for full post details)
  #291 (permalink)
 enderqa 
Chicago, IL
 
Experience: Beginner
Platform: RTrader
Trading: CL
 
Posts: 15 since Apr 2013
Thanks: 9 given, 1 received

@Dudetooth,

Do you have an way to calculate the days to expiration (DTE) for a given open? In the SPAN files CME disseminates, they give the options year and month, very similar to an options symbol. For example: Natural Gas, 201501. This is obviously NGF15 (with some strike price).

I tried looking at the Type records on CME's Web page and nothing jumped out.

Is there a DTE in the SPAN files somewhere? Or do you have to calculate it yourself? Or is there an elegant way to find this information?

Thanks in advance.

Reply With Quote
 
(login for full post details)
  #292 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


enderqa View Post
@Dudetooth,

Do you have an way to calculate the days to expiration (DTE) for a given open? In the SPAN files CME disseminates, they give the options year and month, very similar to an options symbol. For example: Natural Gas, 201501. This is obviously NGF15 (with some strike price).

I tried looking at the Type records on CME's Web page and nothing jumped out.

Is there a DTE in the SPAN files somewhere? Or do you have to calculate it yourself? Or is there an elegant way to find this information?

Thanks in advance.

The line you are looking for is the Type B line in the CME SPAN file.

The Type B for NG would look something like this:
B NYMON OOF201402 201402 003124330005000002000030000330000000014520500000001000020140128NG

CME maps out what is found in the span files found here:
https://www.cmegroup.com/confluence/display/pubspan/Type+B+-+Expanded

From that you will find that there is a 7 digit string (characters 73-79) that is the Time to Expiration (in years). In the above example that would be 0145205 ... multiply by 0.000365 and you get the DTE.

Also, there is a 8 digit string (characters 92-99) which is the Expiration Date as YYYYMMDD (20140128 in the example above).

Hope this helps.

Reply With Quote
The following 2 users say Thank You to Dudetooth for this post:
 
(login for full post details)
  #293 (permalink)
 enderqa 
Chicago, IL
 
Experience: Beginner
Platform: RTrader
Trading: CL
 
Posts: 15 since Apr 2013
Thanks: 9 given, 1 received


Dudetooth View Post
The line you are looking for is the Type B line in the CME SPAN file.

The Type B for NG would look something like this:
B NYMON OOF201402 201402 003124330005000002000030000330000000014520500000001000020140128NG

CME maps out what is found in the span files found here:
https://www.cmegroup.com/confluence/display/pubspan/Type+B+-+Expanded

From that you will find that there is a 7 digit string (characters 73-79) that is the Time to Expiration (in years). In the above example that would be 0145205 ... multiply by 0.000365 and you get the DTE.

Also, there is a 8 digit string (characters 92-99) which is the Expiration Date as YYYYMMDD (20140128 in the example above).

Hope this helps.

Thank you, I missed this when I was looking through their Web page! I really appreciate all the time you've given me.

-enderqa

Reply With Quote
 
(login for full post details)
  #294 (permalink)
 volemont 
Zurich, Switzerland
 
Trading: Futures
 
Posts: 55 since Dec 2013
Thanks: 104 given, 75 received

Hi,

Does anyone have historical SPAN files and would be willing to kindly share those? The files on the CME FTP ftp.cmegroup.com only go back to January 2013.

Thanks

Reply With Quote
 
(login for full post details)
  #295 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


volemont View Post
Hi,

Does anyone have historical SPAN files and would be willing to kindly share those? The files on the CME FTP ftp.cmegroup.com only go back to January 2013.

Thanks

CME temporarily posted 2012 data at one time, though the formatting was different. It was only there for a short period of time, and unfortunately I did not grab it.

I don't know of anyone on the thread who did grab the data. Sorry.

Reply With Quote
 
(login for full post details)
  #296 (permalink)
Cdanitius
Singapore
 
 
Posts: 3 since Apr 2015
Thanks: 2 given, 0 received

Hi everyone!

I read through the forum pages, thank you for the information all of you have provided - you lot are really awesome to share all of this ^^

I am currently figuring out how to pull the data for the SGX (Singapore Ex.) and CME for the TSI Iron Ore.
Was playing around with your excel - XLS SPan 04b3 - it works perfectly, but does your excel support the SGX pa2 files?


It seems like I can only scan for positions for NYB and CME only for now :/

Cheers!
Cdan

Reply With Quote
 
(login for full post details)
  #297 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


Cdanitius View Post
It seems like I can only scan for positions for NYB and CME only for now :/

Each commodity has to be coded into the spreadsheet so that the correct lines of data can be found. You can, however, add as many as you would like. You just need to know the product code and a little about VBA to modify the file. If you want to know how, give me an example of a commodity you'd like to track and I can walk you through the process.

Reply With Quote
 
(login for full post details)
  #298 (permalink)
Cdanitius
Singapore
 
 
Posts: 3 since Apr 2015
Thanks: 2 given, 0 received

Hi Dudetooth!

Thanks for the really prompt reply.

I am currently trying to track the FE and FEF (Iron Ore CFR China (62% Fe Fines) Swaps & Futures respectively) and also the TIO (Iron Ore as well) from the NYMEX exchange.

The SGX parameter files can be found here. (I can't post it here, its under the SGX main website, under Clearing,Span Download PM Cycle)

I tried playing around with the automated download for the SGX files but they change the urls every day.(not just the date)

How did you manage to extract data from the pa2 files?

Really appreciate if you could give me a mini walkthrough =)

Cheers!

Reply With Quote
 
(login for full post details)
  #299 (permalink)
 Dudetooth 
Steubenville Ohio
 
Experience: Intermediate
Platform: OX, OEC, RJO
Trading: Options on Futures
 
Posts: 266 since Sep 2012
Thanks: 30 given, 274 received


Cdanitius View Post
Really appreciate if you could give me a mini walkthrough

So, I have good news and bad news.

The bad: Adding the SGX exchange and commodities should have been fairly easy, but as I started to look at their pa2 files I noticed that they are missing the Type P line of data. The Type P data has info on where to place the decimals in the prices and if the data is missing it will throw an error in the spreadsheet. I will check, but I don't think that is an easy fix for this in the spreadsheet.

The good: Adding TIO and any other CME or NYB commodities is fairly easy.
1) In Visual Basic Editor, open the Function P34Bstring in the Functions module.
2) Find Case "ZB" and right above that line insert this:
Case "TIO"
strS(0) = "P NYMICT OOF"
strS(1) = "3 NY-TIO1001"
strS(2) = "4 NY-TIO01"
strS(3) = "B NYMICT OOF"
strS(4) = "82NYMTIO TIO FUT"

Final code with TIO inserted should look like this:
Case "SI"
strS(0) = "P CMXSO OOF"
strS(1) = "3 CX-SI 1001"
strS(2) = "4 CX-SI 01"
strS(3) = "B CMXSO OOF"
strS(4) = "82CMXSI SI FUT"
Case "TIO"
strS(0) = "P NYMICT OOF"
strS(1) = "3 NY-TIO1001"
strS(2) = "4 NY-TIO01"
strS(3) = "B NYMICT OOF"
strS(4) = "82NYMTIO TIO FUT"
Case "ZB"
strS(0) = "P CBT17 OOF"
strS(1) = "3 17 1001"
strS(2) = "4 17 01"
strS(3) = "B CBT17 OOF"
strS(4) = "82CBT17 17 FUT"

3) Go to the Function T8string
4) Find Case "ZB" and right above that line insert this:
Case "TIO"
str5 = "82NYMICT TIO OOF"

Final code with TIO inserted should look like this:
Case "SI"
str5 = "82CMXSO SI OOF"
Case "TIO"
str5 = "82NYMICT TIO OOF"
Case "ZB"
str5 = "82CBT17 17 OOF"

Where did I find these lines of code? All it takes is a little footwork to find the info in the pa2 file. Open a CME pa2 file in Notepad.exe. The first line to look for is the Type P line of data. You know that TIO is traded on NYMEX, so do a search for "p nymtio". You'll find "P NYMTIO FUTIRON ORE FUTURE002000 000050000000000000000001USD$STD 00 IRON ORE FUTURES", but that line if for the futures. The next line down is what you need: "P NYMICT OOFIRON ORE 62% FE002002 000050000000000000000001USD$STD 00EUROIRON ORE 62% FE,(TSI) CHINA APO". If you noticed, you don't need all of it for the code just the first characters through "OOF". Right underneath that are the Type 3, Type 4 and Type B lines. There are a lot of Type B lines, one for every futures and options contract. You have to go down quite a bit until you see "B NYMTIO FUT" which is a futures contract turn into "B NYMICT OOF" which is the first options contract. Notice that the Type B is "B NYMICT" not "B NYMTIO". Many commodities use a different code for the options than for the futures. That's what gave me the clue to search for "ICT" when finding the Type 82 line of data.

Confused yet? It sounds a lot worse than it really is. Compare the initial strings you see in the code to the lines of data you see in the pa2 file and after a while it should make sense.

With those two modifications in the spreadsheet I ran the numbers for an option and it match what CME Core was showing for it.


I attached a text file with the two modified modules so you can see the proper formatting because some of the spaces are removed in the lines of code when I post them in the reply.

Attached Files
Register to download File Type: txt Modified Code.txt (10.6 KB, 10 views)
Reply With Quote
The following user says Thank You to Dudetooth for this post:
 
(login for full post details)
  #300 (permalink)
Cdanitius
Singapore
 
 
Posts: 3 since Apr 2015
Thanks: 2 given, 0 received

Hey Dudetooth,

Really appreciate your long and detailed post - I apologize for the really late reply.

I had played around with what you had mentioned, but I didn't make much progress. It was eating quite a bit of my time so I had decided to pass what you had said over to my colleagues who understood more about programming so they could tackle it in their free time. They were pretty amazed to see what you had come up with.

Instead, I had customized a spreadsheet that had links to my current position files (in the format they are recorded) - it converts and exports all of them as CSV files, and runs them through PC - Span to gather the margins I require. Not the most efficient way, but it currently works for me.

Thank you for your time! Awesome forum and people =)

Reply With Quote


futures io Trading Community Traders Hideout Options > PC-SPAN


Last Updated on March 6, 2021


Upcoming Webinars and Events

NinjaTrader Indicator Challenge!

Ongoing

Journal Challenge w/$1,800 in prizes!

May 7

EdgeProX - A Simple Choice For The Active Trader w/Morad Askar @ Edge Clear …

Elite only

The Cold Hard Truth: Maybe I Am Not Good Enough w/Chris Gray @ Earn2Trade

Elite only
     



Copyright © 2021 by futures io, s.a., Av Ricardo J. Alfaro, Century Tower, Panama, Ph: +507 833-9432 (Panama and Intl), +1 888-312-3001 (USA and Canada), info@futures.io
All information is for educational use only and is not investment advice.
There is a substantial risk of loss in trading commodity futures, stocks, options and foreign exchange products. Past performance is not indicative of future results.
no new posts