srimani202002
New Member
- Joined
- Feb 22, 2014
- Messages
- 13
Hi All,
I have a challenging requirement at hand and am sure the Excel gurus on this forum can help me. I am really a novice with Excel VBA...So please please help me. I am specifying my scenario / project as below:
I have a weblink NSE - National Stock Exchange of India Ltd. which opens by default in 'Capital Market' tab besides which there are two other tabs 'Derivatives' and 'Debt Market'. As part of my project, the code should select 'Derivatives' tab and select the 'All Historical Reports' hyperlink. On clicking that, a new page opens where we need to pass two parameters 'Select Report' and 'Date' (DD-MM-YYYY) - this is a calendar field. As part of the macro, I need it to select 'Participant wise Open Interest' in the 'Select Report' parameter and appropriate date. Now the option on the website is to choose a single date as part of the date parameter. However, I want an excel facility where I would be able to select a date range say '01-01-2014' to '21-02-2014' and this in turn would fetch me all the csv files within that date range. So for my selection, it should download all the individual csv files for specific dates within the mentioned range that is available as part of the archieve database on the website. The files should be downloaded to a specific folder in my laptop (I need it to be specified somewhere on my user form) and will have a specific naming convention which would have a date string for identification (default file name is like that only). This would complete the first part of my activity.
The second part of the project is to be actually using data from all of those csv files thus downloaded and consolidate the same in an excel file based on dates. For eg, each sheet will have data in the below fashion. What I need to do is to get each of the data columns 'Future Index Long' to 'Total Short Contracts' copied to individual Worksheets named as per the 'Client Type' viz. Client, DII, FII, Pro as per the data. Each single row should be for a specific date (in column A of each sheet) and the date should be chosen from the date string within the file "Participant wise Trading Volume (no. of contracts) in Equity Derivatives as on February 21, 2014"...so it should have 21-Feb-2014 in cell A2 and then the corresponding data. The Row1 would have all the headers pre-defined in each of the sheets. This should be repeated automatically for all the files (date based) that is stored in the specified folder. Lastly, each of the columns should be summed up to show the total in specified cell...need to note that the data will keep on adding on daily basis and hence the summation row should be dynamic.
Hope the above requirement can be done through an excel project. I would be extremely grateful if somebody can help me develop this VBA Macro.
Thanks
Shankhajit
<TBODY>
</TBODY><COLGROUP><COL><COL span=14></COLGROUP>
I have a challenging requirement at hand and am sure the Excel gurus on this forum can help me. I am really a novice with Excel VBA...So please please help me. I am specifying my scenario / project as below:
I have a weblink NSE - National Stock Exchange of India Ltd. which opens by default in 'Capital Market' tab besides which there are two other tabs 'Derivatives' and 'Debt Market'. As part of my project, the code should select 'Derivatives' tab and select the 'All Historical Reports' hyperlink. On clicking that, a new page opens where we need to pass two parameters 'Select Report' and 'Date' (DD-MM-YYYY) - this is a calendar field. As part of the macro, I need it to select 'Participant wise Open Interest' in the 'Select Report' parameter and appropriate date. Now the option on the website is to choose a single date as part of the date parameter. However, I want an excel facility where I would be able to select a date range say '01-01-2014' to '21-02-2014' and this in turn would fetch me all the csv files within that date range. So for my selection, it should download all the individual csv files for specific dates within the mentioned range that is available as part of the archieve database on the website. The files should be downloaded to a specific folder in my laptop (I need it to be specified somewhere on my user form) and will have a specific naming convention which would have a date string for identification (default file name is like that only). This would complete the first part of my activity.
The second part of the project is to be actually using data from all of those csv files thus downloaded and consolidate the same in an excel file based on dates. For eg, each sheet will have data in the below fashion. What I need to do is to get each of the data columns 'Future Index Long' to 'Total Short Contracts' copied to individual Worksheets named as per the 'Client Type' viz. Client, DII, FII, Pro as per the data. Each single row should be for a specific date (in column A of each sheet) and the date should be chosen from the date string within the file "Participant wise Trading Volume (no. of contracts) in Equity Derivatives as on February 21, 2014"...so it should have 21-Feb-2014 in cell A2 and then the corresponding data. The Row1 would have all the headers pre-defined in each of the sheets. This should be repeated automatically for all the files (date based) that is stored in the specified folder. Lastly, each of the columns should be summed up to show the total in specified cell...need to note that the data will keep on adding on daily basis and hence the summation row should be dynamic.
Hope the above requirement can be done through an excel project. I would be extremely grateful if somebody can help me develop this VBA Macro.
Thanks
Shankhajit
| Participant wise Trading Volume (no. of contracts) in Equity Derivatives as on February 21, 2014</SPAN> | ||||||||||||||
| Client Type</SPAN> | Future Index Long</SPAN> | Future Index Short</SPAN> | Future Stock Long</SPAN> | Future Stock Short</SPAN> | Option Index Call Long</SPAN> | Option Index Put Long</SPAN> | Option Index Call Short</SPAN> | Option Index Put Short</SPAN> | Option Stock Call Long</SPAN> | Option Stock Put Long</SPAN> | Option Stock Call Short</SPAN> | Option Stock Put Short</SPAN> | Total Long Contracts</SPAN> | Total Short Contracts</SPAN> |
| Client</SPAN> | 157193</SPAN> | 157237</SPAN> | 282748</SPAN> | 286423</SPAN> | 632186</SPAN> | 760023</SPAN> | 663642</SPAN> | 702066</SPAN> | 77819</SPAN> | 46325</SPAN> | 81479</SPAN> | 42602</SPAN> | 1956294</SPAN> | 1933449</SPAN> |
| DII</SPAN> | 577</SPAN> | 1571</SPAN> | 8468</SPAN> | 7782</SPAN> | 6</SPAN> | 0</SPAN> | 50</SPAN> | 320</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 0</SPAN> | 9051</SPAN> | 9723</SPAN> |
| FII</SPAN> | 64693</SPAN> | 57412</SPAN> | 188025</SPAN> | 187697</SPAN> | 200397</SPAN> | 217738</SPAN> | 187648</SPAN> | 225101</SPAN> | 19892</SPAN> | 15578</SPAN> | 20879</SPAN> | 17313</SPAN> | 706323</SPAN> | 696050</SPAN> |
| Pro</SPAN> | 95877</SPAN> | 102120</SPAN> | 245490</SPAN> | 242829</SPAN> | 953923</SPAN> | 1202954</SPAN> | 935172</SPAN> | 1253228</SPAN> | 66848</SPAN> | 36865</SPAN> | 62201</SPAN> | 38853</SPAN> | 2601957</SPAN> | 2634403</SPAN> |
| TOTAL</SPAN> | 318340</SPAN> | 318340</SPAN> | 724731</SPAN> | 724731</SPAN> | 1786512</SPAN> | 2180715</SPAN> | 1786512</SPAN> | 2180715</SPAN> | 164559</SPAN> | 98768</SPAN> | 164559</SPAN> | 98768</SPAN> | 5273625</SPAN> | 5273625</SPAN> |
<TBODY>
</TBODY><COLGROUP><COL><COL span=14></COLGROUP>
