• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1585
  • Last Modified:

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?
0
XK8ER
Asked:
XK8ER
  • 5
  • 4
  • 2
  • +1
1 Solution
 
Scott Fell, EE MVEDeveloperCommented:
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
 
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 MVEDeveloperCommented:
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 5
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now