0

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!

2 Answers 2

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

Formula output


Source data

Source data

5
  • Thanks! That’s getting me much closer to what I want, but still it’s not quite right. I tried that for cell A2, and it returned the following: “$-0.13 (-180%).” I tried adding “/100” between “)” and “&,” but that gave me this: “$-0.13 (-0.018).” The correct value is -1.80%. Any ideas for how I can fix it? Jul 26, 2021 at 4:48
  • I think I’ve mostly figured it out! Here’s my input: “=dollar(GOOGLEFINANCE(A2,"change"))&" ("&GOOGLEFINANCE(A2,"changepct")&"%)",” which returns this: “$-0.13 (-1.8%).” However, I’d still like to have the percentages always have 2 decimal places and for the plus sign to be displayed in front of positive values. I’d usually do a custom number format, but I don’t know how to do that for cells with multiple formulas. Do you know how? Jul 26, 2021 at 5:12
  • I was able to come up with a workaround. First, I added two columns: C containing only price change and D containing only percent change. Then, I entered “=GOOGLEFINANCE(A2,"change")” into cell C2. Next, I entered “=GOOGLEFINANCE(A2,"changepct")” into cell D2. Then, I formatted the numbers to my liking for rows C and D. Finally, I entered “=TO_TEXT(C2)&" ("&TO_TEXT(D2)&")” into cell B2 and hid rows C and D. The result was this: “-$0.13 (-1.80%)” – exactly what I want! Thanks again for your help. I definitely wouldn’t have been able to do it without you. Jul 26, 2021 at 5:56
  • @1dareu2mov3 I've modified the formula. Refer the screenshots for output.
    – Tedinoz
    Jul 27, 2021 at 6:07
  • The answer by @doubleunary is a better, more efficient and elegant solution. googlefinance(A2, "change") and googlefinance(A2, "changepct") are only called once.
    – Tedinoz
    Jul 27, 2021 at 6:19
1

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%" 
)
& ")"
2
  • I'm getting a $ sign in the percentages -$0.25 (-$0.33%). Suggest the percentages should read text(googlefinance(A2, "changepct") / 100, "+0.00%;-0.00%;±0.00%" )
    – Tedinoz
    Jul 27, 2021 at 6:11
  • Thanks Tedinoz, edited the answer to remove the superfluous $ signs. Jul 27, 2021 at 7:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.