Downloading Stock Prices into OpenOffice/LibreOffice Calc

stock-prices

Sample OpenOffice Calc file to import Yahoo! stock quotes and historical data into spreadsheet

In the example, I enter Yahoo! stock symbols into cells A5 till A12.  Cell A12 is a dummy variable – more about it later.  To retrieve the stock details, i.e. price, stock name, last date, bid, ask and volume, I select cells B5 till G11, enter “=GETSTOCKDETAILS(A5:A12)” and then press the keys control-shift-enter together.  It will retrieve the quote details in a single web query.

Often, you might want to refresh the quote info as the quotes might change during the day.  To do that, just change the dummy variable (cell A12) to another value.  This will force another fetch from the Yahoo! server.

The historical data is imported on a similar principle.  I hope the 2 examples in the file is self-explanatory.  The function name for historical data is GETSTOCKHISTORY.

Downloading Stock Prices into Excel Spreadsheet
Downloading Stock Prices into Google Spreadsheet

19 thoughts on “Downloading Stock Prices into OpenOffice/LibreOffice Calc”

  1. I tried your OOo spreadsheet with my Canada stocks (i.e BNS-PR.TO), and it worked OK.
    My only problem is that your example is limited in regards to the number of stocks (32) for which data I want to retrieve.
    I am NEW into OOo, have not ANY experience in VBA editing, so, please, could you please guide me on how to adapt you example to my number of stocks?
    Thank you VERY much in advance.

    DR.

    1. I tried setting the macro security to the lowest setting but I can’t seem to get any of the Macros to run. PLS help!!

        1. It works now. I saved and closed the spreadsheet opened it and now it updates properly. How do I add more rows of stock symbols? Can I install this code into another spreadsheet? How?

  2. Amour,

    Just want to convey my thanks for sharing this. I got it working and even made mods to use my list of stocks. I wish you well in this new year and beyond!

  3. Amourtan, thanks for the samples. I too had problems at first but following Mike’s issue I opened and saved a few times and the primary example began to work. If I overwrite an existing symbol with a new one in any of the column A cells of the first table, the price and details update without me selecting anything or using Ctrl Alt Enter. I just hit Enter. The markets are closed now, on Friday night so I can’t fully determine how it would go during the business day. But it’s a great start. I’m running OpenOffice 4.11

    I did some VB and ASP programming years ago but I’m awfully rusty now. I’d really appreciate your help with another example listed in the comments of the macros.

    I went into the macros and worked with the examples to just print info on screen with no issues. But the comments above GetStockPrices function give an example to list symbols in A3 to A6 and then put the formula provided in curly brackets in corresponding B col cells and then execute. First, the formula in the comments mentions GetPrices which I assume should be GetStockPrices. That’s what I used in the cells in B column, otherwise following the directions in the comments..

    I have to do the Macros, Run and point at GetStockPrices to get it going and then it malfunctions with “wrong number of parameters” and the watch window has cSymbol = aStockSymbols(i) ‘ get single symbol from array highlighted. I couldn’t get Watch to work to determine but I think the selection isn’t passing to the function, but that begs the question why the main routine works. Perhaps there’s an issue with the newer OOffice vs the older versions?

    Any assistance you can give would be much appreciated. Also I have one final question. Is there any service (pay per or not) that gives actual real time data and not the time delayed info this routine accesses? I have a java app from my broker but I have to re-enter the values into my SS. If I could just click a refresh button or something and have the sheet populate, that would be Heaven. I’ve got a very complex but useful SS for my trading. A real time update is all that’s missing.

    Thanks!
    Brian

  4. If you want the spreadsheet to work, you’ve to follow the instructions exactly, the correct functions are GETSTOCKDETAILS and GETSTOCKHISTORY. The passage is pretty short and concise.

    I’m not sure about real-time quotes as I’m a buy-and-hold investor.

  5. Today is March 20, 2015. I am not sure when the sample spreadsheet macros stopped working, but sometime arounf March 1, 2015 I could not get data for more than one stock (the first in the list) to download. I had been using your spreadsheet in LibreOffice Calc for several months before it stopped working. Today, I think I have traced the problem (bug?), and have gotten my copy to work as intended again. The problem is in the construction of the stock list incorporated in the URL request which is transmitted to yahoo.com. I changed your original code:

    FROM THIS:

    Function GetStockPrices( aStockSymbols )
    cSymbols = “”
    For i = 0 To UBound( aStockSymbols )
    If i > 0 Then
    cSymbols = cSymbols + “&” ‘<—-+++++++++++
    EndIf
    cSymbol = aStockSymbols(i) ' get single symbol from array
    cSymbols = cSymbols + "s=" + cSymbol '<—-+++++++++++
    Next

    ' Use this URL to get a stock symbol from Yahoo.
    ' For example, this URL…
    ' http://quote.yahoo.com/d/quotes.csv?s=RHAT&s=MSFT&f=sl1d1t1c1ohgv&e=.csv
    ' would return a CSV file with the prices for both Red Hat and Microsoft.
    ' Similarly, we can form a URL for a single stock, based upon the parameter.
    cURL = "http://quote.yahoo.com/d/quotes.csv?&quot; + cSymbols + "&f=l1nd1bav&e=.csv"

    TO THIS:

    Function GetStockPrices( aStockSymbols )
    cSymbols = "s=" ' 0 Then
    cSymbols = cSymbols + “+”
    EndIf
    cSymbol = aStockSymbols(i) ‘ get single symbol from array
    cSymbols = cSymbols + cSymbol ‘<—-+++++++++++
    Next

    ' Use this URL to get a stock symbol from Yahoo.
    ' For example, this URL…
    ' http://quote.yahoo.com/d/quotes.csv?s=RHAT&s=MSFT&f=sl1d1t1c1ohgv&e=.csv
    ' would return a CSV file with the prices for both Red Hat and Microsoft.
    ' Similarly, we can form a URL for a single stock, based upon the parameter.
    cURL = "http://quote.yahoo.com/d/quotes.csv?&quot; + cSymbols + "&f=l1nd1bav&e=.csv"

    I think Yahoo changed their tag format . Hope this helps.

  6. Thanks for the spreadsheet. It is the only one that I could find for LO. I am primarily interested in the historical data which works fine with LO version 5.1.4.2. However it only shows 45 rows of historical data (I would like 400 or so) and it shows dates in ascending not descending order. Are there simple modifications that I could make to the macros to make these changes. If not can you direct me elsewhere. I am a 84 yr old newbie with some experience with Basic but not with Basic in LO
    Thanks

  7. Hi Mal, I would think the number of rows of data imported depends on the number of rows selected before executing the macro.

  8. Can you highlight 500 rows, key in the formula and then press shift+ctrl+enter? I tried and it works. Please follow the instructions in the article closely. Thanks!

  9. After I deleted the existing array your instructions worked fine. Thanks.
    Now the last piece of the puzzle. Are there any minor modifications to the macro the reverse the row order?

  10. Absolutly grandiose work! I’m totaly happy with this – works great.
    I manage about over 900 stocks at various places over the world; now with your api (smf-Extension seems doesn’t work anymore at libreoffice 5.3.)
    Thanks a lot and excuse my terrible english . . . 🙂

    Luc Boncoeur

    P.S.: is there any plan to make a libreoffice-Extension ? . . .

    1. No plan to make a Libre extension. However, if you do make one, you can email me and I’ll post it including references to your good deed. 🙂

Leave a Reply