How to add daily stock price history col in mysql table in php using Cron Job

Question

I am using cron job to update the stock price in my MySQL table which overwrites the daily price in the same col using Cron Job. Now I want to save daily closing price in a column to calculate Moving averages. I have more than 500 stocks on my table.

mysql_query("UPDATE `stock table` 
set 
    current_price='$current_price',
    change='$change',
    perchange='$perchange',
    post_modified='$updatetime',
    update='$update'
where ID='$postid'");

What is the php code to add a col for the price when the date changes for 500 stocks rows?

Are you just asking how to add a column to a table? A Google search for "mysql add column" should probably help with that. It's not really clear to me what the issue is here. — David, Jun 06 '17 at 11:43
The code I have is currently overwrites the stock price as soon as cron. But now I want to save stock price every day in the new col.. lets say for 200 days, so that price data can be used to calculate Moving average of the stock. My guess is INSERT...ON DUPLICATE KEY UPDATE should work but i need expert advise. — KUMAR, Jun 07 '17 at 05:19

score 0 · Answer 1 · answered Jun 06 '17 at 11:58

Kumar,

No one will write the code for you... if you have made an attempt at writing the code and if you are stuck, then someone can help out as to what's wrong with your code.

That said, if I understand what you are trying to achieve: you want a script that runs on a specified schedule (CRON) and fetches the stock prices of all 500 stocks and stores it in your stock table.

If that is the case--

Take a look at Yahoo! Finance API
Making use of that, you can write your script to fetch the prices. Doing a quick Google search, yielded this: https://benmarshall.me/php-stock-market-api/ Do not know how accurate/reliable etc. it is...

Give it a try and write the code yourself... In general never copy/paste otherwise you won't learn anything (among other reasons).

Hope this helps!

Best,

-Rush

Thanks for the advise. I am not an php expert. Problem is to start saving the stock price history to calculate Moving average which currently not happening, it just overwrites. My guess INSERT...ON DUPLICATE KEY UPDATE should work but i need expert advise. — KUMAR, Jun 07 '17 at 05:23

How to add daily stock price history col in mysql table in php using Cron Job

1 Answers1