VBA quickly transforms the data format to import CBOT settlement price history data into the database

Recently processed in the CBOT program, there is a CBOT historical data need to import into the database, but the table format is more special, first look.

This is the data exported from the CBOT trading software, the corresponding contract from B to H is 1,3,5,7,8,9,11 month, but the year is not fixed, for example, 2010-1-12 corresponding contract 2010 years January contract, that is SF10, But the next few lines of the same column, 2010-1-17, are the 2011-year January contract, SF11, the next year's contract.

To import this table into the database to form the CBOT history to use, you need to turn it into the following.

For simplicity, according to the change month rule, the month of Change 14th is a node, the original data in accordance with this rule first split into multiple files, because the historical data is not a lot of this can also be processed, if particularly many of this method needs to be improved.

Place the contract corresponding to the point at the back of the first line, which needs to be adjusted for each file.

Use the following program to transform the data in each row into a new table.

Sub GetDate () Dim pDate As String Dim Heyue (Ten) As String Dim price (+) As String ' deposits the contract into an array heyue (1) = Sheets (1). Range ("J1"). Value Heyue (2) = Sheets (1). Range ("K1"). Value Heyue (3) = Sheets (1). Range ("L1"). Value Heyue (4) = Sheets (1). Range ("M1"). Value Heyue (5) = Sheets (1). Range ("N1"). Value Heyue (6) = Sheets (1). Range ("O1"). Value Heyue (7) = Sheets (1). Range ("P1"). Value ' loops by row for i = 1 to 8 pDate = Worksheets (1). Cells (i, 1) ' Loop column for k = 1 to 7 worksheets (2). Cells (i * 7-7 + k, 1) = PDate ' Date worksheets (2). Cells (i * 7-7 + K, 2) = Heyue (k) ' Contract Worksheets (2). Cells (i * 7-7 + k, 3) = Worksheets (1). Cells (i, K + 1) ' Closing price Next nextend Sub 

Due to the special table data, using a hand-assisted processing method, I hope you have a better way.