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

[Xipooo]

I know this isn't specifically related to your question, but take it from someone who has written their own stock analysis Access databases... you need to use SQL server for your data tables. Access will not store the quantity of data you need to hold.

Also, it appears your doing 1 second intervals, or perhaps even bid by bid data. You may find your data exceeds the file size limitations with just 1 weeks worth of data from a single symbol. If you are planning on doing any calculations of the data on top of that, you will find it better to perform your calculations within SQL Server via stored procedures. Access will not give you actionable results if it has to pull the data through a linked table to do the calculations. I say this all from very much first hand experience.

[ItsMe]

... Access will not store the quantity of data you need to hold...

I wonder if, from a money perspective, Azure SQL Database (PaaS) would be feasible. At least you would be able to pay as you go.

[Xipooo]

I wonder if, from a money perspective, Azure SQL Database (PaaS) would be feasible. At least you would be able to pay as you go.

Storage choices can get tough. Although Azure is nice in that you can expand your size at will (same with AWS), it is fairly costly to do so. My personal database which calculates buys and sells on a daily basis, takes about 9 hours to process on a 12 core processor with 8 gigs of ram. The SQL DB varies in size from 250Gb to 350Gb depending on how far back I want my data to go. Most of that is in a table that is filled with my calculation data though, and not the stock data itself. I only pull daily results... he looks to be going with trade by trade data. Stock volume for a single stock could range from 100 transactions in a day (almost never that low) to 1 million transactions. Additionally the CSV files typically come as one file per symbol, or one file per day depending on the data you're requesting and the service you subscribe to. In that way, a single table holding only that stocks information is the most common way the data is organized.

What I'm getting at here is that Azure might be a good starting point, but in the end you want your own local server doing the work. If I were him I'd be setting his beefy server up to run just SQL server and nothing else. Then build an Access client that just asks what's going on once in a while. Let the SQL Server and stored procedures do the work.

[ItsMe]

... Let the SQL Server and stored procedures do the work.

With the stats you posted, it seems best to have an on premises server. There are some premium hardware offers using PaaS, though. One thing that seems certain, Access or SQL Express isn't going to cut it as an RDBMS.

[Hswift]

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

I tried this and Microsoft Access still froze. Here is a link to the data I am trying to do this to (data contains Quotes only, no Trades) and the code I am using.
https://www.dropbox.com/s/frre6qb3oi...erial.zip?dl=0
Please try it on your end and let me know if Access freezes on you too.

Thanks

[CJ_London]

as per my suggestion in post #13 this took a few seconds to run

SELECT c.row, c.quotedate, c.timestamps, iif(p.bidorask= "B",p.pricequote,c.pricequote) AS bidprice, iif(p.bidorask="A",p.pricequote,c.pricequote) AS askprice, iif(p.bidorask= "B",p.quotevolume,c.quotevolume) AS bidvolume, iif(p.bidorask="A",p.quotevolume,c.quotevolume) AS askvolume
FROM table1 AS C inner join table1 AS P on p.row=c.row-1

[Hswift]

Thanks Ajax, that worked!


Xipooo, thanks for the info as well. Xipooo, I am a relative begginer programmer and just have experience writing simple scripts, I've never done any sort of work with databases (aside from what I've used Access for as described in this thread).

How would I go about using a SQL server exactly? Would I simply install Access onto a SQL server and operate Access as if it were on my personal desktop? Or is it more complex then that?

[Hswift]

Hi Ajax,

I just ran the code from post #21 on the data in the link from post #20 and found an error seems to come up in the last 4 columns of every other row. See examples from the results below.

row 1534= The last 4 columns of this row are wrong.
row 1534 current results=1534,20151129,170155704,208825,208825,18,1 9
row 1534 corrected results=1534,20151129,170155704,208825,208850,19,2 9

row 1535= This row is correct.
row 1534 current results= 1535,20151129,170155704,208825,208850,19,28

row 1536= The last 4 columns of this row are wrong.
row 1536 current results=1536,20151129,170155708,208850,208850,29,2 8
row 1536 corrected results= 1536,20151129,170155708,208825,208850,19,29

Any idea why this is happening?

Anyway, thanks so much for your help so far one way or another!

(P.S. As I think you may have noticed, one of the columns in the data in the link from post 20 is spelled wrong. It is written as “timsetamps” instead of “timestamps”. I corrected this spelling mistake before running the code, but still found the errors mentioned above. thnx again!)