1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with creating new chart after every data refresh

Discussion in 'Discuss Data Visualizations and Charting' started by ebutu555, Aug 26, 2017.

  1. ebutu555

    ebutu555 New Member

    Messages:
    6
    Hi All,

    I'm extracting live data (Index Options)from website and have set the refresh frequency as 5 minutes. So, the data is getting updated every 5 minutes and I've created a clustered column chart to monitor columns OI, Change in OI and Volume for both Calls/Puts section. Now, instead of seeing the data update in the same chart after data refresh, I want a new clustered column chart to be created after every data refresh. So, for the refresh rate of 5 minutes, 72 charts should be created for the market period of 6 hours. This would allow to analyze the Calls/Puts build-up better. Also, for the column LTP (Last Traded Price), instead of column chart, I want to plot a line chart for different strike prices. For instance, I want to see the price updates for strike prices 8000, 8100, 8200, 9000, 9100, 9200 alone(both calls & puts section). I'm not sure how to achieve this. Any help would be very much appreciated.

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,777
    Ebutu555

    Firstly, Welcome to the Chandoo.org Forums

    Can't the import area be setup as a Table or with Dynamic Named Formula so that as each update happens, the charts will update automatically?
    ebutu555 likes this.
  3. vletm

    vletm Well-Known Member

    Messages:
    2,854
    ebutu555
    You wrote: For instance, I want to see the price updates for strike prices 8000, 8100, 8200, 9000, 9100, 9200 alone(both calls & puts section).
    Could You use this sample?
    Make selections by activating cells from Your top of chart sheets as needed.

    Attached Files:

    ebutu555 and Thomas Kuriakose like this.
  4. ebutu555

    ebutu555 New Member

    Messages:
    6
    Thanks a lot for taking time to answer and coming up with a solution.

    But, I'm looking for a new chart to be created sequentially every time the data sheet is updated. I don't want to see the updates reflecting in the same chart. To be more precise

    Data update at 9 am --> OI Chart (9 am)
    Data update at 9.05 am --> OI Chart (9.05 am)
    Data update at 9.10 am --> OI Chart (9.10 am)

    This will allow me to compare the current data update with the recent past data update visually and also quickly.
  5. ebutu555

    ebutu555 New Member

    Messages:
    6
    T
    Thanks for taking time to answer.

    Data update in one single chart is already working fine. But, I want a new chart to be created after every data refresh.
  6. ebutu555

    ebutu555 New Member

    Messages:
    6
    Sorry, I just realized your solution is for part of my question and it does a fantastic job for selecting random strike prices. Thanks, much appreciated.
  7. vletm

    vletm Well-Known Member

    Messages:
    2,854
    ebutu555
    Could You make sample file after 09:00 update?
    as well as 09:05
    as well as 09:10
    >> three files!
    After that would be more clear to see,
    how and what that 'MainSheet' would change.
    >> What would those 'OI Chart (9 am)', 'OI Chart (9.05 am)' and so would include? ... those three charts which are in that file or what?
    >> You need ONLY ONE CHART! - NOT 72 same kind of charts!
    You just select which update You would like to see!
  8. ebutu555

    ebutu555 New Member

    Messages:
    6
    Ok, I'm attaching the following files to show the data change that is happening

    Option Analysis_9.22 am
    Option Analysis_9.45 am
    Option Analysis_9.52 am

    Option Analysis_Expected Solution file is what I'm expecting. A new chart for each data update, where as the previous chart should serve as history and it's data reference should be unlinked as it should not get updated. For instance, in tab Change in OI, at 9.52 a new chart will be created, where as the chart created at 9.45 will remain same and should not be updated. Same applicable for chart created at 9.22 am. Like this, for all tabs from 9.30 to 3.30 pm.

    Hope I'm able to state clearly my requirement.

    Thanks in advance.

    Attached Files:

  9. vletm

    vletm Well-Known Member

    Messages:
    2,854
    ebutu555 ... Your texts has moving elements!
    Really only SAMPLE - okay?

    ! now from 9AM to 3PM
    ! all '5min' data have to have same layout ... same number of rows !
    > I cannot test this version because 'NO DATA CONNECTION' <
    You should make that connection ONLY to 'DataSheet'! only means ONLY!
    MainSheet is for charts!

    > I copied those Your NEW charts ... You can set those almost as You need.
    > [ RUN ] ... would make copy every 5 minutes between 9AM to 3PM
    > [ ReSet ] ... helps sometimes
    > yellow time dropdown (maybe later something else) for selecting those saved 'charts'. Now, there are only 'few saved = copied data'! 09:00 - 09:30!
    > Upper selectors as in previous version

    Ideas?

    Attached Files:

    ebutu555 and Thomas Kuriakose like this.
  10. ebutu555

    ebutu555 New Member

    Messages:
    6
    Thanks for sparing your time and coming up with a solution.

    Unfortunately, I was stuck with other work and couldn't try this today, but the one time I tried, the excel stopped working after I clicked the Run button.

    By the way, your idea is good and dropdown idea is good as well. Just wondering is it not possible to paste the charts one after another sequentially time wise, in stead of dropdown option. Is that difficult or will it affect the excel performance?

    One more thing is noticed is when I select top selector the data is displayed 1 strike price lesser. I mean, for instance, when I select 10000, the chart displays the data for 9950. It happens for all the tabs.
  11. vletm

    vletm Well-Known Member

    Messages:
    2,854
    the excel stopped working after I clicked the Run button. ..
    Q: how stopped? Did it give any message?
    It 'works' only from 09:00 to 15:00!
    It should check time every two seconds and if any of those 12 times of hour, it would save that moments 'DataSheet'.

    As I wrote yellow time dropdown (maybe later something else) >>
    sequentially time wise
    I quickly did one SAMPLE (= NOT READY) of that.
    > You gotta select FROM&TO hours/minutes with yellow dropdowns
    > Do not even touch those 'real time-cells'!
    > also it's possible to length for one chart (STEP)
    >> after those press [Show] <<
    NOTE! There are data ONLY from 09:00 to 09:30 and some of those are same!

    Selecting 10000 ... hmm?
    I tested many times and it works here!
    If select 10000 then color of that cell will change
    If select above of 10000 then 5 cells color will change
    If select below of 10000 then 10 cells color will change
    Center (with number) cells with yellow color will show with charts.
    What would be different there?

    Attached Files:

    Thomas Kuriakose likes this.

Share This Page