I would like for stockapi to retain all stock update information in the database.
crick - November 6, 2006 - 21:27
| Project: | Stock API |
| Version: | HEAD |
| Component: | Code |
| Category: | feature request |
| Priority: | normal |
| Assigned: | Unassigned |
| Status: | active |
Jump to:
Description
Instead of having the old data replaced with the new data when the cron is run, it would be nice if the every update was retained, this would allow us to generate our own graphs using the data from the database.
#1
I don't think it would be a problem to do that. For the keeping of the data part, it might just be as simple as removing line 128. You would have to be careful about database size, if you were tracking a large number of stocks with a small check interval.
For the other part, that might have to wait until I can get in there and refactor the code to allow a choice of services. Then, one of the choices could be something like a custom or local option.
#2
I really appreciate your response, I think your advice has set me toward the right direction although simply deleting line 128 is going to cause a lot of duplicate entry errors when the market is not trading. Deleting line 128 helped me out a great deal because it showed me a little bit about how stockapi actually works (instead of me trying to read and guess).
I'm a php/mysql amateur and I'm still not real sure about what all stockapi is doing to/with the database. I don't know what all would be involved with achieving my idea but I wanted to post it and see what kind of responses I might get, so here goes...
Currently when a stock is looked up, that stocks's data is fetched from yahoo and the data is then stored in a "row" in the stockapi table. When the cron is run, the data in each row is deleted and replaced.
What I would like my site to do is when a $stock is looked up, a table is created {stockapi_$stock}. {stockapi_$stock} would be the same format as {stockapi} but without the $symbol=$stock[0] field.
I hope this makes sense, basically i'd like to be able to have a separate table for each stock. each stock table would resemble the stockapi table except it wouldn't have a field for the stock symbol and it would be sorted by date and time. we could treat the updating and inserting process much in the same way that it is now. for instance when a symbol is looked up, the stockapi table is inspected and if the symbol is found, the data is deleted and replaced, if the symbol is not found, its inserted into a new row. instead of doing that we could check for date and time, if the market is closed the function stockapi_fetch is going to return identical date and time during each cron run, we either delete and replace, or do no action?
#3
disregard my previous comment, I'm sitting here working on it now and I think what I described above is either impossible or extremely overcomplicated? let me know what u think.
#4
Ok, i've been working on the module today and I did end up deciding that I'd like for each symbol to have it's own table for organizational reasons.
here is a snippet of what I have come up with so far:
starting at line 125
function stockapi_save($stock) {
$symbol = $stock[0];
if (!empty($symbol)) {
stockapi_delete($symbol);
stockapi_insert($stock);
stockapi_tblcreate($symbol);
}
}
function stockapi_insert($stock) {
return db_query("INSERT INTO {stockapi}
VALUES ('%s', '%s', %f, %f, %f, %f, %f, %d, '%s', '%s', %d)",
$stock[0],
$stock[1],
$stock[2],
$stock[3],
$stock[4],
$stock[5],
$stock[6],
$stock[7],
$stock[8],
$stock[9],
time());
}
function stockapi_tblcreate($symbol) {
return db_query("CREATE TABLE IF NOT EXISTS stockapi_$symbol (
symbol varchar(10) NOT NULL default '',
name varchar(50) NOT NULL default '',
current_price float NOT NULL default '0.0',
change_amt float NOT NULL default '0.0',
opening float NOT NULL default '0.0',
high float NOT NULL default '0.0',
low float NOT NULL default '0.0',
volume int(10) NOT NULL default '0.0',
last_date varchar(10) NOT NULL,
last_time varchar(8) NOT NULL,
updated int(10) NOT NULL default '0',
UNIQUE KEY symbol (symbol)
) TYPE=MyISAM /*!40100 DEFAULT CHARACTER SET utf8 */;");
}
I've tried a few different things to try to get the data from yahoo into the new tables but haven't been able to get it to work.
#5
Now that the next version of stockapi is where I'd like it to be for performance, my next goal is to write stockhistory module, which can import years of information regarding stocks. I'll be working on this, but I'm not sure yet if this will live within StockAPI directly.