Solved

php and mysql to store stock prices

Posted on 2014-03-02
12
1,421 Views
Last Modified: 2014-03-04
hello there,
I would like to store stock prices in database (mysql) by using php.
the stock prices move up or down and the stats change about every 1 second or 2 seconds.
for example:

EUR/USD
Mar 03, 2014 - 11:54 33pm
BUY 1122333
SELL 4455888

since the numbers are changing every time I would like to store this information so that we can have archives and later go back and see for how much it was at what time..

my question would be which format would you guys recommend we use in the mysql?
0
Comment
Question by:XK8ER
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39899811
If you captured 1 per second, that would be 60 per minute * 8 hrs of trading * 5 days a week * 52 weeks or just under 7.5 million rows of data per year.  If you did 30 per minute you would end up with 3.7 million rows of data per year.

Do you really need to capture that much?  What if you captured 60 per minute in a temp table, then only stored the high/low for that hour. No you have just 2,080 rows of data and this will be much more manageable especially if you have a shared server.

Instead of storing this info, why not just use an api like yahoo finance?  http:Q_28377681.html#a39898470
0
 
LVL 109

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39900303
Technically speaking you can do this, but I get the sense you're about to wander out into the currency trading waters.  If you're thinking of doing something like that, you might want to just take all of your money and challenge Tiger Woods to a winner-take-all round of golf.  The outcome with respect to your money will be the same as trading currencies against the professionals, but at least you will get to say you played a round with Tiger.

That aside, you can track the value of a security over time with this table definition (pidgin code, but you get the idea)

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
symbol VARCHAR(8) NOT NULL DEFAULT ''
when TIMESTAMP
value DECIMAL(10,3) NOT NULL DEFAULT 0.0

If you want to track the spreads, you need to replace "value" with two columns, "bid" and "ask."

That's it.  The rest is just data gathering and analysis.  You can get daily historical data from a variety of places.  You might want to contact your stockbroker and see if the firm offers a data feed for its investors.  A Bloomberg feed is rather expensive but it's essential for a professional trader since it gives actionable information in real-time.
0
 
LVL 16

Expert Comment

by:hankknight
ID: 39900445
I was involved in a similar project and I discovered that microseconds can make a HUGE difference.  For that reason, I recommend using MemSQL instead of MySQL.
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
LVL 1

Author Comment

by:XK8ER
ID: 39900457
thanks for helping! sorry I forgot to mention that this project is to try and help out a friend involved in stocks, and that I only need to record the last 6 months of data..
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39900487
Instead of recreating the wheel, I would just use an api.  Yahoo has all this info.  Is it important to know the changes per second? or just the day summary?
0
 
LVL 1

Author Comment

by:XK8ER
ID: 39900527
this information is not provided by anyone out there and its not exactly the high/lows, its a data provided by a specific broker with its own price.
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39900548
It's not important to know the changes per second unless you can trade with sub-second speed.  And if you want the last 6 months of data, you might want to explore the YQL interface:
http://developer.yahoo.com/yql/
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39900552
this information is not provided by anyone out there and its not exactly the high/lows, its a data provided by a specific broker with its own price.
What are you talking about?  Can you be a little more specific, with examples?  Thanks.
0
 
LVL 1

Author Comment

by:XK8ER
ID: 39903394
I did, this is an example

EUR/USD
Mar 03, 2014 - 11:54 33pm
BUY 1122333
SELL 4455888
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39903610
I see that example.  What I don't see is the common sense "why" behind those lines of information.  The date/time is pretty obvious (it would be stored in a DATETIME column using the ISO-8601 standard), but what does EUR/USD mean?  Is it a ratio?  Or is it a trade symbol for a Forex transaction?  And the BUY vs SELL values do not make any sense.

Are you still missing anything here?  Please let us know, thanks. ~Ray
0
 
LVL 1

Author Comment

by:XK8ER
ID: 39903631
not missing anything..
do you recommend using this for the 4 columns?

id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
stockSymbol VARCHAR(8) NOT NULL DEFAULT ''
when TIMESTAMP
buyValue DECIMAL(10,3) NOT NULL DEFAULT 0.0
sellValue DECIMAL(10,3) NOT NULL DEFAULT 0.0

Open in new window

0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 39903642
Yes, that looks fine to me.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
AJAX pass along a variable 3 46
SQLite with Mobile Development 4 20
Row insertion failed. Array 5 47
Echo Multiple values from multiple records 19 40
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

860 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question