Solved

php and mysql to store stock prices

Posted on 2014-03-02
12
1,360 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
Comment Utility
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 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
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
Comment Utility
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
 
LVL 1

Author Comment

by:XK8ER
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
I did, this is an example

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

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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 108

Expert Comment

by:Ray Paseur
Comment Utility
Yes, that looks fine to me.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Creating and Managing Databases with phpMyAdmin in cPanel.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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 …

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now