Help with creating new chart after every data refresh

[ebutu555]

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.

 

[Hui]

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?

 

[vletm]

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.

 

[ebutu555]

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.

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.

 

[ebutu555]

T

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?

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.

 

[ebutu555]

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.

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.

 

[vletm]

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!

 

[ebutu555]

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!

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.

 

[vletm]

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?

 

[ebutu555]

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?

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.

 

[vletm]

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?