locked
Financial Quote Data Conversion RRS feed

  • Question

  • Hello, I have financial quote data that looks as follows. Note: The “qtype” column signifies whether the quote was a Bid(B) or an Ask(A).

    row,qdate,qtimestamp,qprice,qtype,qvolume
    1,20151130,110342316,208650,B,287
    2,20151130,110342360,208675,A,281
    3,20151130,110342364,208650,B,275
    4,20151130,110342366,208650,B,273
    5,20151130,110342408,208675,A,280

    I need to convert this data so that each row shows both the
    Bid price/volume and the Ask price/volume (with a new row being
    created each time the Bid and/or Ask has a change in price and/or volume).

    For instance, the example above would look as
    follows after the conversion (note: the first row from the example
    above could not be included below because it did not have a previous
    row to get the Ask price and volume from).


    row,qdate,qtimestamp,bidprice,bidvolume,askprice,askvolume
    2,20151130,110342360,208650,287,208675,281
    3,20151130,110342364,208650,275,208675,281
    4,20151130,110342366,208650,273,208675,281
    5,20151130,110342408,208650,273,208675,280


    I've been having trouble doing this conversion in Access. Any code examples of how to do this conversion in Access would be much appreciated.

    Thanks!
    P.S.
    The .csv file on my desktop that contains the data I'm trying to convert is 2 gigs in size. I plan to break it up into 20 pieces, then do this conversion 100mb at a time, however I'd ideally like to do it to the full 2 gigs all at once. Thanks again!


    Tuesday, December 22, 2015 12:48 AM

All replies

  • Is the Row number consistent throughout the 2 gig file?

    Is this 2 gig file on a single commodity?


    Build a little, test a little

    Tuesday, December 22, 2015 9:33 PM
  • >>>The .csv file on my desktop that contains the data I'm trying to convert is 2 gigs in size. I plan to break it up into 20 pieces, then do this conversion 100mb at a time, however I'd ideally

    like to do it to the full 2 gigs all at once.<<<

    If you want to import .csv file to Access, you could use the TransferText method to import or export text between the current Microsoft Access database or Access project (.adp) and a text file. You could refer to below code:

    DoCmd.TransferText acImportDelim, , "TableName", ".csv file path", False

    otherwise you could refer to below code to get value from .csv file:

    Dim fso As Variant
    Dim objStream As Variant
    Dim objFile As Variant
    Dim dataItem() As String
    i = 0
    
    Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists("D:\April.csv") Then
       Set objStream = fso.OpenTextFile("D:\April.csv", 1, False, 0)
    End If
    Do While Not objStream.AtEndOfStream
       strLine = objStream.ReadLine
       Debug.Print strLine
    
       dataItem = Split(strLine, ",")
    
       For Each v In dataItem
           Debug.Print v
       Next v
    
       i = i + 1
    Loop

    For more information, click here to refer about DoCmd.TransferText Method (Access)


    Wednesday, December 23, 2015 8:13 AM