﻿ Google sheets - How to get YTD price change for a stock

Using `=GOOGLEFINANCE("HKG:9988","changepct")` you can get the price change since open or last open of the exchange. However as more long-term investor you might want to see the difference for a longer period of time. I use Alibaba as an example in this post, this is not a recommendation or financial advice on that specific stock, it is just used as an example.

Using the below formula and replacing "HKG:9988" (Alibaba) with another stock ticker you can get the price change for the current year (YTD - Year to date). You can see my guide on how to find the correct stock price here. Here is the formula for getting year-to-date price:

``````=100*(googlefinance("HKG:9988","price")/index(googlefinance("HKG:9988","price",
date(year(TODAY())-1,12,31)),2,2)-1)
``````

NOTE: Depending on your locale you might have to use `;` rather than `,`:

``````=100*(googlefinance("HKG:9988";"price")/index(googlefinance("HKG:9988";"price";
date(year(TODAY())-1;12;31));2;2)-1)
``````

The formula is quite simple, we take todays price:

``````(googlefinance("HKG:9988","price")
``````

We find the price on the 31st of December of last year:

``````googlefinance("HKG:9988","price",date(year(TODAY())-1,12,31)
``````

Since this returns a table rather than just the value we have to take a specific cell from this table, which is why we use the index function to get the closing price:

``````index(googlefinance("HKG:9988","price",date(year(TODAY())-1,12,31)),2,2)
``````

From here on after it is just math, the above will be:

``````=100*((119,4/227,6)-1) // == -47,53
``````

Which is what Alibaba has lost this year so far.

Using ticker from another cell

You can get the ticker from another cell by referencing the cell. In the below example I take the value from R1:

``````=100*(googlefinance(R1,"price")/index(googlefinance(R1,"price",
date(year(TODAY())-1,12,31)),2,2)-1)
``````

Which gives the following result:

Yes, I added this at the beginning of 2022, where Alibaba was up for a change.

That is it

I hope you find this helpful, let me know what you think in the comments down below! :)