Daily automatic update of stock quotes via REST API and Cronjob. Or is there a more sophisticated way?

Question

I'm building a stock database with WordPress. There is a custom Post Type stocks with the fields last_price and ytd_return (ACF). The database will hold around 2000 stocks.

For the stock quotes I plan to use alphavantage (https://www.alphavantage.co) which offers a REST API to pull updated quotes.

As I only want end-of-day data I plan the following: Every night run a "CronJob" inside Wordpress which pulls the updated stock quotes for all the stocks and calculates YTD performance.

Now the question: What is the most straightforward way to do this? Is it wise to loop through all my posts (stocks) and use wp_remote_get() to update the data? I expect to get problems with php execution times...

Or is there a better way to accomplish my goal with another php solution? For example I thought about setting up an own table in the sql db and insert the new data there.

Answer 1

I would recommend you to separate your data from wp-data. To realize your idea, setup a new data table for time series data. Write a php script to request your data from desired source and create a cronjob. If your hoster does not provide cronjob creation, use a cronjob service. Depending on the amount of data you request, you probably have to create approx. 10 cronjobs.

Then, create a new table for YTD performance values and create a new php script to calculate YTD performance. This should work very quickly, so you will probably need only one cronjob (I suppose, maximum 4 cronjobs).

Now you will have the time series data and the performance data in two separate tables. To show YTD data in your WordPress, create a shortcode in your functions.php like I did here.