Solved

php and mysql to store stock prices

Posted on 2014-03-02
12
1,485 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 53

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 110

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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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 53

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 110

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 110

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 110

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 110

Expert Comment

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

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

688 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