Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a series from multiple columns and (multiple) files
Hi,
This a long read. Sorry about that. I am new to VB and have no clue as to how I can proceed. Any guidance would be appreciated. I have to construct a single time series from a number of futures contracts over a 52 year period. The data is in multiple .csv files and arranged such that the data for each contract traded in a given quarter is given in blocks. Typically, there are 4 or 5 contracts being traded at any given point in time. Each file contains data for 1 year, starting from January 1952 till June 2004. CBOT - End-of-Day Futures Bulk Download 1972. Wheat Futures (Empty line here) Date,Symbol,Month Code,Year Code,Open,High,Low,Close,Settlement,Volume,Open Interest 19720103,W,H,1972,162.75,163.38,161.88,161.88,161. 88,481,6774 19720104,W,H,1972,161.63,161.88,161.13,161.63,,653 ,6746 19720105,W,H,1972,162.25,163.75,162.13,162.88,,580 ,6671 : : : : : : : : : : 19720103,W,K,1972,155.5,156.5,155.5,155.63,155.63, 250,3413 19720104,W,K,1972,155.5,156.5,155.13,156.25,156.25 ,274,3421 19720105,W,K,1972,157,157.88,156.88,157.63,,240,34 34 : : : : : : : : : : (Data on settlement prices is not available in a lot of cases) The date information has to be transformed to the dd/mm/yyyy format and retained along with the close price, volume and open interest. H, K, N, U and Z refer to the March, May, July, September and December contracts, respectively. The time in days till the end of the contract or time to maturity (trading days, excluding weekends and holidays) needs to be computed for each futures contract as well. I need to create a series of close prices based on certain criteria. There are four different criteria and each needs to be applied separately to the data. 1. The series begins with the nearest contract (in the data given above, it would be the March 1972 contract). Two working days before the contract month, say the 27th of February, we switch to the May contract (if it is not traded, then to the July contract), and then to the July contract and so on till we reach the end. 2. The series is constructed as a weighted sum of the nearest and the next nearest contract being traded, the weights being a polynomial function of the time to maturity. Again, the switch from one contract to the next is done two working days before the contract month. The weights sum to one. 3. The series is constructed weighted on the volumes of all contracts being traded. 4. Same as (3) except that weighting is based on the open interest. The weights sum to one in (2), (3) and (4). Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating one trendline for multiple series | Charts and Charting in Excel | |||
Creating a series of cells with multiple incrimenting characters | Excel Worksheet Functions | |||
Creating a High-Low-Close chart with multiple series? | Charts and Charting in Excel | |||
combining multiple columns from multiple files | Excel Discussion (Misc queries) | |||
Creating a High-Low-Close chart with multiple series? | Excel Discussion (Misc queries) |