[Solved] Pull stock price from Internet

[MoeinIA]

In a spreadsheet, can you connect a cell to the internet to retrieve a current market price of a stock?

[Hagar Delest]

Hi and welcome to the forum!

See if that helps: [Solved] Stock Market Functions LO Calc Extension.
But also Stock market data.
Some other topics that may be related: query about Calc topics with 'stock'.

Please add [Solved] at the beginning of the title in your first post (top of the topic) with the *EDIT button if your issue has been fixed.

[Zizi64]

Yes you can do it, but not all of types of internet pages with. The static HTML pages and the data of the HTML tables will work in the AOO.

From the menu,
- Apache OpenOffice: Insert - Link to external data - ...
- LibreOffice: Data - Streams - ...

[USAoo]

Hi,

you can query Yahoo Finance to get a simple string return for any symbol you need and parse out the market price or any other data you need. See code below with examples for crypto prices, you can adjust as required. It's rough but does the job. Hope this helps.

Code: Select all

sub getQuotes

	' Sample string from https://query1.finance.yahoo.com/v7/finance/quote
	' {"quoteResponse":{"result":[{"language":"en-US","region":"US","quoteType":"CRYPTOCURRENCY","quoteSourceName":"CoinMarketCap","triggerable":true,"firstTradeDateMilliseconds":1410908400000,"regularMarketTime":1621025882,"regularMarketPrice":49273.43,"fullExchangeName":"CCC","tradeable":false,"sourceInterval":15,"exchangeDataDelayedBy":0,"exchange":"CCC","exchangeTimezoneName":"Europe/London","exchangeTimezoneShortName":"BST","gmtOffSetMilliseconds":3600000,"market":"ccc_market","esgPopulated":false,"marketState":"REGULAR","symbol":"BTC-USD"}],"error":null}}

	dim oDoc as object
	dim oSheet as object
	
	dim oDocWebBTC as object
	dim oDocWebETH as object
	dim opts(0) As New com.sun.star.beans.PropertyValue

	sWebAddrBTC = "https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US&region=US&corsDomain=finance.yahoo.com&fields=regularMarketPrice&symbols=BTC-USD"
	sWebAddrETH = "https://query1.finance.yahoo.com/v7/finance/quote?lang=en-US&region=US&corsDomain=finance.yahoo.com&fields=regularMarketPrice&symbols=ETH-USD"
	
	opts(0).Name = "Hidden"
	opts(0).Value = True
	
	' Get market prices from Yahoo finance
	
	' Get BTC
	oDocWebBTC = StarDesktop.loadComponentFromURL(sWebAddrBTC, "_blank", 0, opts)
	sWebStringBTC = oDocWebBTC.Text.String
	sRegularMarketPriceBTCStringRoughCut = Mid(sWebStringBTC, InStr(sWebStringBTC, "regularMarketPrice"), 40)
	iRegularMarketPriceBTCStringRoughCutCommaPosition = InStr(sRegularMarketPriceBTCStringRoughCut, ",")
	sRegularMarketPriceBTCString = Mid(sRegularMarketPriceBTCStringRoughCut, 21, iRegularMarketPriceBTCStringRoughCutCommaPosition - 21)
	
	' Get ETH
	oDocWebETH = StarDesktop.loadComponentFromURL(swebAddrETH, "_blank", 0, opts)
	sWebStringETH = oDocWebETH.Text.String
	SRegularMarketPriceETHStringRoughCut = Mid(sWebStringETH, InStr(sWebStringETH, "regularMarketPrice"), 40)
	iRegularMarketPriceETHStringRoughCutCommaPosition = InStr(sRegularMarketPriceETHStringRoughCut, ",")
	sRegularMarketPriceETHString = Mid(sRegularMarketPriceETHStringRoughCut, 21, iRegularMarketPriceETHStringRoughCutCommaPosition - 21)
	
	oDocWebBTC.close(True)
	oDocWebETH.close(True)

	oDoc = ThisComponent
	oSheet = oDoc.Sheets.getByName("Data")
	CellBTC = oSheet.getCellByPosition(1, 1)
	CellBTC.SetValue(CDbl(sRegularMarketPriceBTCString))
	CellETH = oSheet.getCellByPosition(1, 2)
	CellETH.SetValue(CDbl(sRegularMarketPriceETHString))
end sub