CODEWITHSUNDEEP
google-finance
TCH Tank
TCH Tank

Reputation: 13

Is there any way to define an exchange in Google Finance separately?

I have stocks from US and Canadian Companies.

I would like to be able to enter the stock symbol and select the exchange in the adjacent column and put the google finance formula and get the current ticker price.

Currently the only way I can get this done is by manually entering the exchange. For Example: =googlefinance("tse:enb","Name") gets me the name of the stock symbol. I want to be able to enter the ticker symbol in one column and exchange in another column and get the desired output (name in this example) in another column.

Is there a better way to do this?

Upvotes: 1

Views: 1767

Answers (2)

Chris
Chris

Reputation: 1

Reading your post I thought I would share a technique that simplifies the sheet. I have an example using an Array formula for the "Symbol" column and a Lambda formula for the "Name" and "Price" columns ("Price" I added). Using these formulas, all the other columns will be automatically filled in as long as the "Exchange" and "Ticker" entries exist with the correct data. The Array_Constrain wrapper stops the arrays from expanding past the rows with data in the A column.

The link below is an example showing how these techniques can be implemented. As I was building my own sheet I discovered the GoogleFinance() function would not work from within a regular ArrayFormula() which is the reason for the ByRow(Lambda()) function which seems to do the trick. I hope you find this valuable.

Thanks to all the posters, YouTubers, and such which explained all these techniques to me!

Image showing my sheet.

Click Here for My Example Google Spreadsheet

Upvotes: 0

Jack465
Jack465

Reputation: 151

you could solve this issue by setting up your sheet as follows:

Exchange Ticker Symbol Name
NASDAQ APPL =JOIN(":", A2, B2) =GOOGLEFINANCE(C2, "Name")

and then just duplicating the formulas down as required, to make it easy for copy and pasting, I have put the formulas separately below

Symbol: =JOIN(":", A2, B2)

Name: =GOOGLEFINANCE(C2, "Name")

If this wasn't what you were looking for, please let me know and I'll try to help again.

Upvotes: 1

Related Questions