Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Hswift is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    20

    Merge Bid price rows and Ask price rows into (implied) Quote rows. (RE: Stock market data.)

    Hello Accessforums.net community,



    I just started using Microsoft Access and think its a fantastic tool for building databases of stock prices in order to do quantitative financial research. Anyway, I was wondering if someone could please show me how to do the following?

    I have a .CSV file that contains the following data for a stock's price. Notice that a new row forms anytime the Bid or Ask price changes (rather then forming at a set time interval).

    ROW #, TIME (HH:MM:SS), PRICE TYPE, PRICE
    1, 09:00:00, BID, 114.02
    2, 09:00:01, ASK, 114.04
    3, 09:00:01, ASK, 114.03
    4, 09:00:01, ASK, 114.04
    5, 09:00:02, BID, 114.03
    6, 09:00:02, ASK, 114.05
    7, 09:00:03, BID, 114.04

    I need to convert this .CSV's data into a new .CSV that also forms a new row each time the Bid or Ask price changes, but also includes the price of the value (Bid or Ask) that did not change since the last row. The example below shows how the converted .CSV should look.

    (Note: Row 1 below does not include the Ask price because it has no previous row to draw the Ask price from.)

    ROW #, TIME (HH:MM:SS), BID PRICE, ASK PRICE
    1, 09:00:00, 114.02,
    2, 09:00:01, 114.02, 114.04
    3, 09:00:01, 114.02, 114.03
    4, 09:00:01, 114.02, 114.04
    5, 09:00:02, 114.03, 114.04
    6, 09:00:02, 114.03, 114.05
    7, 09:00:03, 114.04, 114.05

    Look forward to your thoughts, thanks for the help!!!
    Last edited by Hswift; 12-05-2015 at 06:11 AM.

  2. #2
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you mean the previous price? and you mean a table rather than .csv file?

    perfectly doable, try something like

    Code:
    c.row, c.time, iif(p.pricetype="Bid",p.price,c.price) as bidprice, iif(p.pricetype="Ask",p.price,c.price) as askprice
    from tblPrices as C, tblPrices as P
    WHERE P.Row =(SELECT Max(Row) from tblPrices WHERE Row<c.Row AND pricetype<>c.pricetype)
    if you table has a mixture of stocks then the query would be
    Code:
    SELECT c.row, c.time, iif(p.pricetype="Bid",p.price,c.price) as bidprice, iif(p.pricetype="Ask",p.price,c.price) as askprice
    from tblPrices as C INNER JOIN tblPrices as P ON C.StockID=P.StockID
    WHERE P.Row =(SELECT Max(Row) from tblPrices WHERE StockID=c.StockID AND Row<c.Row AND pricetype<>c.pricetype)
    if you need to put these back to a .csv file, use transfertext

    not time and type are reserved words, use them at your peril

  3. #3
    Hswift is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    20
    Thanks Ajax!
    I've only used the "Query Design" tool so far and am not sure where to even begin when doing things via code in Access.
    Where would i type the code you mentioned in exactly (perhaps I'd add it as a Module)?
    Once i have typed the code in, how would I apply it to my Table (perhaps I'd apply it somehow via the "Query Design" tool?

    Thanks again!

  4. #4
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    the query design tool creates sql code which is what I have provided -you can see the code if you click on the SQL option on the dropdown at the left end of the ribbon. So open a new query, do not choose a table, click on the SQL option then copy and paste the code I provided to overwrite anything that is there. Once done you can return to the query design grid so see what it looks like.

    Because you did not provide table names or were clear about field names, I've used my own so you will need to change them to match yours. I've already advised about Time and Type being reserved words, you should also not have spaces or non alphanumeric characters in tables and field names - if you do so, enclose names with square brackets which will work most of the time, but not always so it is strongly recommended you change these and get into a good habit.

    No need to apply it to the table - just run the query - it provides the 'view' you require. You should not be storing calculated data in the table - it may change and you will have a bit job to correct it. If you need to manipulate the view in some way run another query off this view or modify the query further

  5. #5
    Hswift is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    20
    Hi Ajax,

    The 5 steps I took to do this are below. Any idea which step I did wrong? I’m guessing the code I entered in Step 4 is where I went wrong?

    Step 1= Screenshot 1 shows the table I’m using (with the column names changed based on your suggestions). Note: the “timestamp” column shows times that are timestamps down to the millisecond, which is why the numbers are so long. The “pricequote” contains the price of a very expensive Futures symbol, which is why its such a large number.

    Click image for larger version. 

Name:	1.jpg 
Views:	21 
Size:	71.6 KB 
ID:	22950



    Step 2= Screenshot 2 has a blue arrow showing the “query design “ tab I pressed.



    Click image for larger version. 

Name:	2.jpg 
Views:	20 
Size:	236.5 KB 
ID:	22951



    Step 3= Screenshot 3 has a blue arrow showing the “SQL View” tab I pressed.



    Click image for larger version. 

Name:	3.jpg 
Views:	19 
Size:	208.4 KB 
ID:	22952



    Step 4= Screenshot 4 shows the code from your example I typed in (Note: You’ll see I tried updating the code from your example accordingly based on the exact column names in the table from step 1. I’m guessing this code update is probably where I made a mistakeJ?



    Click image for larger version. 

Name:	4.jpg 
Views:	19 
Size:	83.2 KB 
ID:	22953




    Step 5= Screenshot 5 has a blue arrow showing the “Datasheet View “ tab I pressed after entering the code in step 4. And a red arrow showing the error message I received right after I pressed the "Datasheet View" tab.



    Click image for larger version. 

Name:	5.jpg 
Views:	21 
Size:	120.4 KB 
ID:	22954





    Look forward to your thoughts, thanks!

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    looks like you have a semi colon after select

  7. #7
    Hswift is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    20
    Hah, that fixed it. Works perfectly now, thanks so much!!!!

    One more question. How can I make the end result include the additional following three columns?

    1. bidvolume= This would come from the according data in the "quotevolume" column in the screenshot below.
    2. askvolume= This would come from the according data in the "quotevolume" column in the screenshot below.
    3. quotedate= This would come from the "quotedate" column in the screenshot below.

    Click image for larger version. 

Name:	1.JPG 
Views:	19 
Size:	111.4 KB 
ID:	22962

    Thanks!

  8. #8
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    same basis as for the prices - switch back to the design view and you will probably see it more clearly

    e.g.

    iif(p.pricetype="Bid",p.quotevolume,c.quotevolume) as bidvolume

    I'm surprised you have a tablee just for one stock - is this all you are analysing or do you have separate tables for each stock?

  9. #9
    Hswift is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    20
    Works perfectly now, thanks Ajax!

    Yes, i have separate tables for each stock. Thanks again!

  10. #10
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Yes, i have separate tables for each stock. Thanks again!
    Sounds like bad design - everytime you add a stock you need to write a new query.

    Consider having one table with an extra column to identify the stock, then you just need one query, as above but slightly modified as suggested in post #2

  11. #11
    Hswift is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    20
    Cool, thanks for the tip!

  12. #12
    Hswift is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    20
    Hi Ajax,

    Access froze when I tried to do this conversion to a Table with over 2 million rows. I'm using a quadcore computer with 24 gigs of Ram, so i don't think lack of computing power is my issue.

    Do you think Access is just not technologically capable of converting 2 million plus rows?
    Or is there just a different approach i should take?

    Thanks!

  13. #13
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Do you think Access is just not technologically capable of converting 2 million plus rows?
    yes - I've run queries on much bigger datasets.
    Or is there just a different approach i should take?
    Not really but

    is your table indexed? Row and stockid should be

    see post#6 on this thread to understand indexing more.https://www.accessforums.net/queries...ish-56724.html

    Do you really want a report of 2 million rows to view at any one time? I suspect not - you would normally restrict it to one or two stocks and perhaps a time period in which case your quote date and perhaps timestamp fields would also be indexed - not clear how your timestamp field is determined - date and time would normally be combined into one field.

    One final thought - if you can guarantee that the row fields is a) contiguous (no gaps in the numbering) and b) the right order (and indexed) you can try this query, although you will not be able to view it in the query grid

    Code:
    SELECT c.row, c.time, iif(p.pricetype="Bid",p.price,c.price) as bidprice, iif(p.pricetype="Ask",p.price,c.price) as askprice
    from tblPrices as C INNER JOIN tblPrices as P ON C.StockID=P.StockID AND P.Row=C.Row-1
    Note this will not return the first record where there is no previous stock

    Finally, advice for the future, show the relevant table fields right at the beginning of the thread, don't keep exposing more as the thread develops.

    If I knew the table was constructed per your post#7 I could have suggested a solution based on the quote date and timestamp fields since this is a more reliable indicator of the previous record. for example, if look at rows 11 and 12 in post #7 - two records one for bid, one for ask with exactly the same time and amounts - it at least begs the question what is the basis for deciding which record comes first and is it possible you have a row with a lower row number but higher date time value compared with a later row

  14. #14
    Hswift is offline Novice
    Windows 8 Access 2013
    Join Date
    Dec 2015
    Posts
    20
    How do I Index the table? I'm not sure if I did it.

    FYI:
    I do not need to view these 2 million quotes in Access. I just need to use Access to convert them accordingly and then export all 2 million of them into a .CSV file. The reason I need to create this .CSV file is because the trading/research platform I'm using requires .CSV files to be in the format I've specified.

    The reason I have 2 million quotes is because the quotes reflect every single change in price and/or volume for one day of a single stock, which adds up very quickly.

  15. #15
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    How do I Index the table?
    go into table design, select the field to be indexed and in the properties select 'indexed - no duplicates' for row and 'indexed - duplicates OK' for stockid

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. help merge rows in query
    By pika2112 in forum Access
    Replies: 1
    Last Post: 12-10-2014, 10:24 AM
  2. Replies: 4
    Last Post: 02-25-2014, 01:09 AM
  3. Replies: 0
    Last Post: 02-09-2012, 05:43 PM
  4. Replies: 2
    Last Post: 08-28-2011, 06:06 AM
  5. merge or concatenate two rows in one
    By vojinb in forum Queries
    Replies: 7
    Last Post: 08-03-2011, 09:15 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums