php and mysql to store stock prices

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?
LVL 1
XK8ERAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
Ray PaseurCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hankknightCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

XK8ERAuthor Commented:
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
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
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
XK8ERAuthor Commented:
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
Ray PaseurCommented:
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
Ray PaseurCommented:
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
XK8ERAuthor Commented:
I did, this is an example

EUR/USD
Mar 03, 2014 - 11:54 33pm
BUY 1122333
SELL 4455888
0
Ray PaseurCommented:
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
XK8ERAuthor Commented:
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
Ray PaseurCommented:
Yes, that looks fine to me.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.