Import stock market data into PostgreSQL

09.2013 / Category: / Tags: |

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:

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:

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:

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

Happy investing.

Visit us on facebook:

0 0 votes
Article Rating
Notify of
Newest Most Voted
Inline Feedbacks
View all comments
10 years ago

* 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.

9 years ago


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?

CYBERTEC Logo white
CYBERTEC PostgreSQL International GmbH
Römerstraße 19
2752 Wöllersdorf

+43 (0) 2622 93022-0

Get the newest PostgreSQL Info & Tools

    This site is protected by reCAPTCHA and the Google Privacy Policy & Terms of Service apply.

    CYBERTEC PostgreSQL International GmbH
    Would love your thoughts, please comment.x
    linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram