Excel question

Discussion in 'Programming' started by jakejasonjay, Jan 26, 2012.

  1. I have been trying to find out how to make a streaming number in an existing cell show in another cell as it appeard 5 minutes earlier and continue incrementally in time behind said number. Any help out there? Jay
  2. You would need to create a log file and then write your streaming number to that log file. From there you would have the -5min cell reference the log file.

    This can be done easily with VBA. You may also use another workbook tab instead of a log or database file.
  3. Try using XLQ from Qmatix: http://www.qmatix.com/XLQ.htm

    You can set intraday backfill from tick level precision to the minute increments your looking for. Outside of intraday it keeps historic daily trade data to 1 minute precision and supports weekly and monthly backfills allowing you to quickly retrieve and display wide range of trade data .

    The backfill functionality is mostly a function of your data feed provider: Works with IQFeed, IB and even a few free internet sources for historic data (Yahoo, Google etc.) providers.

    No VBA/DDE/RTD required... XLQ is an add-in that manages real time data feeds and historic data providers. Allows you to use simple excel formulas to populate real time and historic data into excel.
  4. thanks guys but even your suggestions were too advanced for me to implement. It sounds like I need to further educate myself in excel or else I'm going to need some directions. Again, thanks for taking the time to give me your ideas. JAY
  5. XLQ is a bargain for the price. However, if you really need a solution that requires customization, Randy Harmelink's free VBA addin is pretty robust : http://finance.groups.yahoo.com/group/smf_addin/
    It seems to have a lot of the same features as XLQ, but for the most part, it works with Yahoo delayed or realtime quotes. However, you do get the source code, so with some modifications, it's probably adaptable for other feeds as well. I've used it....very slick and quite efficient. However, once again, as we've said many times: beware of the STA (single threaded architecture). That means it can easily get swamped if too many symbols are requested.
  6. Actually, using the method suggested by TJWinston (log data to another sheet), this is fairly easy to implement in Excel without using VBA or AddIns. It involves self-referencing cells and enabling iterative calculations with iterations set to one.

    Assumuing DDE, what data source are you using? When the market opens Sunday night, I will put together a demo spreadsheet for you and post it here.
  7. I'd love to see that technique Kevin....
    it's hard to believe with random ticks hitting the sheet that you can create a log of the "prints" without VBA.
  8. See attached.

    For this to work you have to go into File/Options/Formulas
    and check the "Enable iterative calculation" box. Then set
    "Maximum Interations" to 1 in the combo box just below
    the box you just checked.

    Make sure the DDE link in cell B1 is working and the change
    the value in cell B9 from TRUE to FALSE, and away you go.

    You can change the DDE link, which is currently from MT4,
    to reference whatever DDE server you like by modifying the
    formula in cell B1.
  9. Thx! you taught me something today. I knew it could be done but had never seen it in action - thx again!