Importing stock market data into PostgreSQL

Many people I know are doing some private stock market investment. Some of them just want to become rich people – some are saving for their retirements and some are just doing it for fun. What I have noticed is that the internet is full of people who want to import stock market data into a database so that they can process things. A relational database is just perfect to process this kind of data. The question now is: How can data be imported into PostgreSQL nicely?Usually stock market data is composed of the following fields:

test=# CREATE TABLE t_stock (

d date,

open numeric,

high numeric,

low numeric,

close numeric,

volume int8,

adj_close numeric);

CREATE TABLE

We got a date, some pricing information, and information about the trading volume. To obtain this information many people are using Yahoo! Finance, which is a nice and free source for market data. How can we load this data into PostgreSQL? Creating intermediate files and processing steps is somewhat uncool so we’d better do it in one line (to make sure it is cool and efficient).

PostgreSQL and curl

Yahoo offers a nice API allowing us to fetch directly via HTTP. Data will be returned in CSV format. To download data from the net you can use a UNIX command line tool called curl. Curl is the easiest way to fetch data from the web and display it on the screen. The cool thing now is that PostgreSQL 9.3 is able to read data directly from a pipe through the new “COPY … PROGRAM” mechanism. So why not just attach curl to PostgreSQL?

Here is how it works:

test=# COPY t_stock

FROM PROGRAM 'curl http://ichart.finance.yahoo.com/table.csv?s=%5EGSPC&d=8&e=5&f=2013&g=d&a=0&b=3&c=1950&ignore=.csv'

CSV HEADER;

COPY 16021

What this line does is loading data for the S&P 500 index since 1950. The shortcut for the S&P 500 is “^GSPC”. The rest of those parameters are just here to define the timeframe and so on. If you just want to import data easily, we suggest to go to the Yahoo! Website, lookup your favorite stock and just copy / paste the “Download to Spreadsheet” link at the end of the page.

In our case data has been imported into our PostgreSQL table nicely:

test=# SELECT * FROM t_stock LIMIT 10;

d | open | high | low | close | volume | adj_close

------------+---------+---------+---------+---------+------------+-----------

2013-09-04 | 1640.72 | 1655.72 | 1637.41 | 1653.08 | 3312150000 | 1653.08

2013-09-03 | 1635.95 | 1651.35 | 1633.41 | 1639.77 | 3731610000 | 1639.77

2013-08-30 | 1638.89 | 1640.08 | 1628.05 | 1632.97 | 2734300000 | 1632.97

2013-08-29 | 1633.50 | 1646.41 | 1630.88 | 1638.17 | 2527550000 | 1638.17

2013-08-28 | 1630.25 | 1641.18 | 1627.47 | 1634.96 | 2784010000 | 1634.96

2013-08-27 | 1652.54 | 1652.54 | 1629.05 | 1630.48 | 3219190000 | 1630.48

2013-08-26 | 1664.29 | 1669.51 | 1656.02 | 1656.78 | 2430670000 | 1656.78

2013-08-23 | 1659.92 | 1664.85 | 1654.81 | 1663.50 | 2582670000 | 1663.50

2013-08-22 | 1645.03 | 1659.55 | 1645.03 | 1656.96 | 2537460000 | 1656.96

2013-08-21 | 1650.66 | 1656.99 | 1639.43 | 1642.80 | 2932180000 | 1642.80

(10 rows)

Of course you can also automate the process easily for stock and bonds you are mostly interested in.

Happy investing.

Visit us on facebook: www.fb.com/cybertec.postgresql

Hans-Juergen Schoenig
Hans-Jürgen Schönig has 15 years of experience with PostgreSQL. He is consultant and CEO of the company „Cybertec Schönig & Schönig GmbH“ (www.postgresq-support.de), which has served countless customers around the globe.