How can I include two formulas in a single cell in Google Sheets?

Question

I’m in the process of making a spreadsheet of stocks that I’m watching in Google Sheets. I want to include the daily price and percentage change in a single column. I found a helpful Docs Editors thread, and I was able to get it working the way it’s formatted in the answer from that thread.

What I’m starting with is the following input (A2 contains a ticker symbol):

=GOOGLEFINANCE(A2,"change")&" | "&GOOGLEFINANCE(A2,"changepct")

That displays this (using random numbers):

1.25 | 3.15

That technically works. However, I’d like the data to display in a different way. This is what I’d prefer:

+$1.25 (+3.15%)

The parentheses are the most important part, but I’d also like to show the dollar sign, percent symbol, and plus symbol if possible (the minus symbol will always show for negative values, but I want to also always see the plus symbol for positive values).

I’ve changed the input to the following:

=GOOGLEFINANCE(A2,"change")&" ("&GOOGLEFINANCE(A2, "changepct")")"

That gives me an error that says, “Formula parse error.” Changing it to

=GOOGLEFINANCE(A2,"change")&" ("&GOOGLEFINANCE(A2, "changepct")

gives a functioning result, but it’s of course missing the closing parenthesis. The cell displays as

1.25 (3.15

What am I doing wrong? Any help is appreciated!

Answer 1

Try this formula:

=if(GOOGLEFINANCE(A2,"change")>1,"+"&dollar(GOOGLEFINANCE(A2,"change"))&"",dollar(GOOGLEFINANCE(A2,"change"))&"")&" "&if(GOOGLEFINANCE(A2,"changepct")>1,"(+"&TO_TEXT(GOOGLEFINANCE(A2,"changepct"))&"%)","("&TO_TEXT(GOOGLEFINANCE(A2,"changepct"))&"%)")

---

Logic:

• use dollar() and to_percent() to format the GOOGLEFINANCE values
• use _to_text()` to converted the formatted values as text.
• combine the text values in a formula, inserting parentheses around the 'Percentage Change'.

---

Formula Output

---

Source data

Answer 2

You apparently want results to look like:

+$1.25 (+3.15%)
-$1.25 (-3.15%)
±$0.00 (±0.00%)

To show the + and ± prefixes, use text() with three fields, one each for positive, negative and zero, and concatenate the results with the & operator, like this:

=text( 
  googlefinance(A2, "change"), 
  "+$0.00;-$0.00;±$0.00" 
) 
& " (" & 
text( 
  googlefinance(A2, "changepct") / 100, 
  "+0.00%;-0.00%;±0.00%" 
)
& ")"