Ted Schlossmacher's free GetQuote extension for OpenOffice.org Calc allows users to access quotes for several types of symbols tracked by Yahoo! Finance. Specifically, the GETHISTORY() function returns a range of past and present quotes.
After installing the extension, try highlighting a 5-column range and then typing =GETHISTORY("PETR4.SA",1,TODAY()-1) (you might need to use semicolons instead of commas) and then pressing Ctrl+Shift+Return. That should provide you with date, open, high, low and close quotes for PETR4, the preferred stock of Brazilian oil giant Petrobras S.A.
My question is: how can I, in one cell, insert a formula that would return me the value of the 5th column of the above array?
This can be done with the INDEX function. You don't need to use ctrl+shift+enter for it to work as it does't return an array.
=INDEX(GETHISTORY("PETR4.SA",1,TODAY()-1),1,5)
The 2 end parameters are row,column, and are a 1-based index into the array.
More information about INDEX can be found on any Excel website, or in the LibreOffice Calc help at https://help.libreoffice.org/Calc/Spreadsheet_Functions#INDEX