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:
|
1 2 3 4 5 6 7 8 9 10 11 |
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:
|
1 2 3 4 |
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 going 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
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.
+43 (0) 2622 93022-0
office@cybertec.at
* You need to save the CSV
* then remove the column headers because most likely it doesn't like them being imported into the cols you setup
* then you need to reformat the date values from mm/dd/yyyy to yyyy-mm-dd
* go to a different col and type ( =concatenate(year(a1),"-",month(a1),"-",day(a1) ) )
* then copy that fmla all the way down to the bottom
* then copy that whole column
* then PASTE VALUES on top of column A1
* then save CSV with a new name just in case..
* then import that new csv into the table you created.
Herb,
this is a nice proposal - and for sure not meant seriously. So take it as idea that there's a solution more to fill data into PostgreSQL. Why it's not seriously? Do you really see such kind of tables without mentioning which kind of stock, fonds or bond is meant? Do you see an ISIN, ticker symbol, whatever in the table?