Quantcast
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Exporting/scraping data from OptionVue to Excel

Discussion in 'OptionVue Forum' started by AKJ, Jan 28, 2016.

  1. AKJ

    AKJ Well-Known Member

    During Kevin Lee's webinar yesterday on JL's site, he shared some screenshots of hit spreadsheet that tracks Butterfly pricing at different days/times for different strikes.

    I was in the process of writing a script to take snapshots of my IB Excel DDE, but paused when I heard Kevin mention that he was now doing this directly from OV.

    Does anyone know of an efficient way to replicate this in OV? That would save me a great deal of time.

    Thanks,

    AJ
     
  2. Capt Hobbes

    Capt Hobbes Well-Known Member

    File > Export, click Line Format to select the fields you want and adjust other settings as needed, then click Export.
     
  3. Kevin Lee

    Kevin Lee Well-Known Member

    Hi

    It's quite a straight forward process :

    1. File - Export. You have to set up the fields you want to download by clicking Line Format.

    2. You get one file for one time slot containing all the expirations you have in the matrix. You'll end up with multiple files every day, each containing data for 30 min time slot in backtrader.

    3. Copy data from the data files into an Excel table.

    4. Use pivot table to organize data in whatever format required for your analysis

    You could write a macro to automate the above process. But doing it manually is quite easy too.
     
    Last edited: Jan 29, 2016
    GreenZone, meyer99 and Gabor Maly like this.
  4. AKJ

    AKJ Well-Known Member

    Thanks Kevin! I am testing this out today, saving the file every half hour.

    One concern is whether each new file would overwrite the previously saved file since there doesn't appear to be any way to add a time-stamp to the file name. We'll see how it works though; maybe this wont be an issue at all and each new file will have some unique extension.
     
  5. Kevin Lee

    Kevin Lee Well-Known Member

    Hi Andrew,

    You don't need to save the live data every half hour. You can wait till end of day or tomorrow to download the data in backtrader mode. Just step forward 30mins and do an export.

    Yes it'll over write the file unless you change the file name. Let's send suggestion to OV to not overwrite but save with unique extension.
     
  6. AKJ

    AKJ Well-Known Member

    Thanks Kevin,

    Just so I understand correctly, what you are doing is stepping through backtrader mode 30 minutes at a time, saving each file manually and giving each file a unique name?

    That seems a little more cumbersome that I would've hoped. I could see that taking 15-20 minutes each day....maybe more if you did it for both SPX and RUT. Definitely something I will suggest to OV as an future upgrade.
     
  7. Kevin Lee

    Kevin Lee Well-Known Member

    Yes, what you described is correct. However, you can save multiple underlying every time. Just specify which underlying you want in the "Selected Assets" box.
    If you automate the process using Excel VBA to read in the multiple files, copying the data and append to a table, and then refresh the pivot table, it'll be quick.

    I tried capturing every 15 minutes through TOS DDE before. The process was not as smooth as I thought. Firstly I have to keep the computer on. Often Excel will crash when I tried to load too many strikes and symbols. For me, that'll be in the middle of night and I'd lose the rest of the day's data. That's why I changed to downloading OV data and I'm quite happy with the process.

    You can also try auto-download every x minutes. It's there but I've never used it though.

    upload_2016-1-29_11-34-20.png
     
  8. Balazs

    Balazs Member

    Hi Kevin,

    I really enjoyed your trader of the month video in the Locke community and I admire your commitment and valuable knowledge about options trading and software related question. Just like posts of Ron (Bertino) and Gabor Maly, and others of course, these types of imputs have helped me a ton in understanding some core parts of this game and making it my own. Even though I am still a beginner (aren't we all), I have gained substantial confidance. On personal note, Singapore is a fantastic city indeed and I enjoy it to the fullest every time I am there.

    Now back to the topic here. I am a ONE user. The simple reason is the speed of the backtrader, the easy to use interface, the price and the fact that it is the first software I ever used for options. ONE lacks the feature of exporting historical data, however, for 79 USD, there is a tool that is called tradinggeeks that can accomplish the same. For some it is perhaps familiar, but for those that this software is new, it can be very useful. (http://www.tradinggeeks.net)
    It is a mac based software that can download ANY historical data from IB, through API. Prices for stocks, ETF's, indexes, futures, currencies and options. You can select the time intervals (down to 1 second) and the timeframe. The data available is really huge. There is one big disadvantage though: The data is exported in a txt file and is a little hard to read if you download a enourmous amount of data. Perhaps there is a way, to import it directly into excel, if so, I would love to hear about it.

    Anyway, this is just for people who trade with IB and ONE, who would love to make the same kind of excel, you showed in your video, it is possible with the help of this tool.
    I hope it helped,

    Balazs

    Disclaimer: I am not afliated in any way with tradingeeks, I am just a humble user of the software.
     
    Murphy Tan and uwe like this.

Share This Page