Hi, everyone,
I m just a beginner on programming excel. I would like to perform such project. May I know how to start which is more properly? And what kind of reference information I should read.
I would like to get the real data from this website
http://www.hkex.com.hk/eng/ddp/Most_Active_Contracts.asp?MarketId=2
(webA)
First, I would like to extract the live data from this web for analyzing the information by excel. Even plot graph for it.
I want to extract the data from the web site with the top 20 high volume of option product vs change in time.
For example:
<table class="table_grey_border" id="table" width="100%"><tbody><tr class="tr_header"><th class="verd_black12b" colspan="2" height="30" width="140">Contract</th> <th class="verd_black12b" width="40">Bid</th> <th class="verd_black12b" width="40">Ask</th> <th class="verd_black12b" width="60">Last Traded</th> <th class="verd_black12b" width="40">High</th> <th class="verd_black12b" width="40">Low</th> <th class="verd_black12b" id="volume" width="60">Volume</th> <th class="verd_black12b" width="80">Prev. Day Settlement Price</th> <th class="verd_black12b" id="nc" width="60">Net Change</th> <th class="verd_black12b" id="oi" width="80">Prev. Day Open Interest</th> </tr> </tbody><tbody id="tbody"> <tr id="tr0" class="tableHdrB1" align="center"><td align="left" width="40">HSI</td><td align="centre" width="110">C Oct-11 - 19000</td><td align="right">173</td><td align="right">200</td><td align="right">195</td><td align="right">301</td><td align="right">180</td><td align="right">300</td><td align="right">293</td><td align="right">-98</td><td align="right">4,069</td></tr></tbody></table>
When extract the data of this option, ADD the current time stamp on the following column. At the same time will extract the next web page information for the particular product.
This is next webpage
http://www.hkex.com.hk/eng/ddp/Most_Active_Contracts.asp?MarketId=1 (webB)
<table class="table_grey_border" id="table" width="100%"><tbody><tr class="tr_header"><th class="verd_black12b" colspan="2" height="30" width="140">Contract Month</th> <th class="verd_black12b" width="40">Bid</th> <th class="verd_black12b" width="40">Ask</th> <th class="verd_black12b" width="60">Last Traded</th> <th class="verd_black12b" width="40">High</th> <th class="verd_black12b" width="40">Low</th> <th class="verd_black12b" id="volume" width="60">Volume</th> <th class="verd_black12b" width="80">Prev. Day Settlement Price</th> <th class="verd_black12b" id="nc" width="60">Net Change</th> <th class="verd_black12b" id="oi" width="80">Prev. Day Open Interest</th> </tr> </tbody><tbody id="tbody"> <tr id="tr0" class="tableHdrB1" align="center"><td width="40">HSI</td><td align="centre" width="110">Oct-11</td><td align="right">18,431</td><td align="right">18,436</td><td align="right">18,432</td><td align="right">18,666</td><td align="right">18,333</td><td align="right">95,759</td><td align="right">18,612</td><td align="right">-180</td><td align="right">90,476</td></tr></tbody></table>
By only grap the particular products such as HSI Oct-11, Last traded figures and volume with the same Time stamp
Then once the volume changes, the extract data will be taken action again and insert an second row with time stamp, then do calculation on the difference between Volume.previous and Volume.Now. And place it under Volume.diff column. And also grap WebB's information as stated above.
Using those data to add some calculation formula to analysis the detail and plot graph.
For the other top 19 volume products will be the same procedures. And if the top 20 volumes changes during the time, the existing top 20 volume will not be deleted. And build a save function for record the whole activities, and the second day all record will be reset.
That is what I want to do. May I know anyone who have ideas what kind of programming I should learn and how I can start?
Thank you very much.
ello
I m just a beginner on programming excel. I would like to perform such project. May I know how to start which is more properly? And what kind of reference information I should read.
I would like to get the real data from this website
http://www.hkex.com.hk/eng/ddp/Most_Active_Contracts.asp?MarketId=2
(webA)
First, I would like to extract the live data from this web for analyzing the information by excel. Even plot graph for it.
I want to extract the data from the web site with the top 20 high volume of option product vs change in time.
For example:
<table class="table_grey_border" id="table" width="100%"><tbody><tr class="tr_header"><th class="verd_black12b" colspan="2" height="30" width="140">Contract</th> <th class="verd_black12b" width="40">Bid</th> <th class="verd_black12b" width="40">Ask</th> <th class="verd_black12b" width="60">Last Traded</th> <th class="verd_black12b" width="40">High</th> <th class="verd_black12b" width="40">Low</th> <th class="verd_black12b" id="volume" width="60">Volume</th> <th class="verd_black12b" width="80">Prev. Day Settlement Price</th> <th class="verd_black12b" id="nc" width="60">Net Change</th> <th class="verd_black12b" id="oi" width="80">Prev. Day Open Interest</th> </tr> </tbody><tbody id="tbody"> <tr id="tr0" class="tableHdrB1" align="center"><td align="left" width="40">HSI</td><td align="centre" width="110">C Oct-11 - 19000</td><td align="right">173</td><td align="right">200</td><td align="right">195</td><td align="right">301</td><td align="right">180</td><td align="right">300</td><td align="right">293</td><td align="right">-98</td><td align="right">4,069</td></tr></tbody></table>
When extract the data of this option, ADD the current time stamp on the following column. At the same time will extract the next web page information for the particular product.
This is next webpage
http://www.hkex.com.hk/eng/ddp/Most_Active_Contracts.asp?MarketId=1 (webB)
<table class="table_grey_border" id="table" width="100%"><tbody><tr class="tr_header"><th class="verd_black12b" colspan="2" height="30" width="140">Contract Month</th> <th class="verd_black12b" width="40">Bid</th> <th class="verd_black12b" width="40">Ask</th> <th class="verd_black12b" width="60">Last Traded</th> <th class="verd_black12b" width="40">High</th> <th class="verd_black12b" width="40">Low</th> <th class="verd_black12b" id="volume" width="60">Volume</th> <th class="verd_black12b" width="80">Prev. Day Settlement Price</th> <th class="verd_black12b" id="nc" width="60">Net Change</th> <th class="verd_black12b" id="oi" width="80">Prev. Day Open Interest</th> </tr> </tbody><tbody id="tbody"> <tr id="tr0" class="tableHdrB1" align="center"><td width="40">HSI</td><td align="centre" width="110">Oct-11</td><td align="right">18,431</td><td align="right">18,436</td><td align="right">18,432</td><td align="right">18,666</td><td align="right">18,333</td><td align="right">95,759</td><td align="right">18,612</td><td align="right">-180</td><td align="right">90,476</td></tr></tbody></table>
By only grap the particular products such as HSI Oct-11, Last traded figures and volume with the same Time stamp
Then once the volume changes, the extract data will be taken action again and insert an second row with time stamp, then do calculation on the difference between Volume.previous and Volume.Now. And place it under Volume.diff column. And also grap WebB's information as stated above.
Using those data to add some calculation formula to analysis the detail and plot graph.
For the other top 19 volume products will be the same procedures. And if the top 20 volumes changes during the time, the existing top 20 volume will not be deleted. And build a save function for record the whole activities, and the second day all record will be reset.
That is what I want to do. May I know anyone who have ideas what kind of programming I should learn and how I can start?
Thank you very much.
ello