GOOGLEFINANCE in Google Sheets to track my portfolio

I wanted to keep a track of my portfolio using google sheet.

e.g: if i buy a share let’s say on 07th April 2021 and i know my trigger price, Target 1 , target 2 & SL. i want google sheet to mark each of them with specific color as soon as CMP matches one and another of trigger price, T1, T2 & SL until i sell them.

Share CMP BUY T1 T2 SL Buy Date Sell Date
HDFC 2450 2400 2550 2600 2390 07/04/2021

How to acheive it, i tried comparing CMP columns with other columns but as the prices keeps changing i am unable to track it.

GOOGLEFINANCE in Google Sheets to get high & low and timestamp of high/low from the day i took trade

I wanted to keep a track of high/low of a trade from the day i took the trade.
for a day i am able to get high/low but how to get from the date i took my trade which is stored in one of cell.

=IFERROR(GOOGLEFINANCE("NSE:"&A2&"","HIGH"))

Converting a googlefinance() market cap to another currency in Google Sheets

I can get the market capitalization of Bank of Montreal with =googlefinance("TSE:BMO", "marketcap"). The result is in US dollars. How do I get it in Canadian dollars?

automation – Googlefinance automatic daily updating

I am creating an index that follows several tickers from 2014 to the present (daily data) by using Google Finance in Google Sheets. I want to add all of the tickers’ new prices at the end of each of day that passes without deleting the previous day’s numbers. In other words, the index builds over time automatically.

Does anyone have insight into how to accomplish this?

Thank you in advance for any help here!

google sheets – How to get AUD price of bitcoin at a particular date using GOOGLEFINANCE

I am trying to find out how to get the price of bitcoin, ethereum, litecoin and bitcoin cash in AUD at a particular date.

I have a table as follows

+------------+-------+
|    Date    | Price |
+------------+-------+
| 16/03/2016 |       |
| 19/04/2016 |       |
| 03/12/2017 |       |
+------------+-------+

I have tried entering using:

=GOOGLEFINANCE("BTCAUD", "price", A2)

However, I get the error:

When evaluating GOOGLEFINANCE, the query for the symbol: 'BTCAUD' returned no data.

Appending current Share Price to Historic Daily Share Price on Google Sheet GOOGLEFINANCE Function Sparkline

I have a sparkline for the daily price for the last 30 days for a share in google sheets using the following formula (F1):

F1 =SPARKLINE(GOOGLEFINANCE(“GOOG”,”price”,Today()-30,Today()))

I can also get the current share price using a second formula (F2):

F2 =GOOGLEFINANCE(“GOOG”,”price”)

I was just wondering if there is a way to append this value (F2 output) onto the end of the sparkline returned from F1, to get a hybrid sparkline with the current SP shown.

Note: I could put the output of the table of historic data provided in F1 (just this bit: GOOGLEFINANCE(“GOOG”,”price”,Today()-30,Today()) into another sheet, and then append the current SP on the bottom of the output… but I wanted to be able to vary the number of historic days and the share in question, and have this for a large number of shares, so a formula solution would be preferred.

Thanks in advance.

google finance – Googlefinance in historical mode (WEEKLY): extra rows and not always aligned to fridays

I am trying to use GOOGLEFINANCE function to query the weekly close price of an instrument (SP500, ticker “.INX”), for the last 52 weeks.

I first tried using the following formula:

​GOOGLEFINANCE("INDEXSP:.INX";"close";TODAY()-365;TODAY();"WEEKLY")

Where TODAY() in this case corresponds to 31/01/2020 (Thursday).

However, when I look at the results, I see that the following issues:
I get 57 results, instead of the expected 52
There are 8 rows correspond to dates not falling on a friday.
I do not get a result for the nearest friday (25/12/2020).

See first screenshot.enter image description here

I then decided to force the start and end dates passed as arguments to the googlefinance function to fall on a friday:

GOOGLEFINANCE("INDEXSP:.INX";"close";SWITCH(WEEKDAY($E3;2);1;TO_DATE(WORKDAY($E3;-1));2;TO_DATE(WORKDAY($E3;-2));3;TO_DATE(WORKDAY($E3;-3));4;TO_DATE(WORKDAY($E3;-4));5;TO_DATE($E3);6;TO_DATE(WORKDAY($E3+1;-1));7;TO_DATE(WORKDAY($E3+1;-1)));SWITCH(WEEKDAY($E4;2);1;TO_DATE(WORKDAY($E4;-1));2;TO_DATE(WORKDAY($E4;-2));3;TO_DATE(WORKDAY($E4;-3));4;TO_DATE(WORKDAY($E4;-4));5;TO_DATE($E4);6;TO_DATE(WORKDAY($E4+1;-1));7;TO_DATE(WORKDAY($E4+1;-1)));"WEEKLY")

With this query, if the dates passed to the start or end date fall on a day other than a friday, then the dates are adjusted to the previous friday (workday).

In this case, the results look very similar but shifted one week before. The issues are the same, I get results for dates that do not fall on a friday, and additionally, I do not get a result for the most recent friday (25/12/2020).

One could think, that GOOGLEFINANCE ​adjusts results for those trading days when the stocks are closed, however there are several examples where I get results for a tuesday (28/07/2020), but I also get results for the previous (24/07/2020) and next friday (31/07/2020), and there are no bank holidays in between.
See second screenshot.

enter image description here

I would appreciate your help.

You can also find a link to the sheet here: https://docs.google.com/spreadsheets/d/1_FmpRNKVGUtbdLUa2msrFej_WEQ9QkAwvXz89c7l4hU/edit?usp=sharing

How to use googlefinance to query a stock price during a specific time period

I’m trying to get the high and low price for particular tickers during the after market and premarket hours. Is there a way to query google finance for highs and lows between the after hours of 1:00-5:00pm EST and 4:00-9:30am EST for the premarket?

List with all the cryptocurrencies available in the GOOGLEFINANCE formula?

I have tried to find documentation on recoverable cryptocurrencies with the GOOGLEFINANCE formula, but I have found none. There is only this general help sheet on the subject, which does not mention cryptos at all.

How can I get GoogleFinance to see the return of a year stock from a specified date?

It works so far, but how could I do it if I wanted to do 365 days from a specified date instead of today? For example, 2020/2/19 and 365 days before.

= GOOGLEFINANCE (A2, "price") / index (GOOGLEFINANCE (A2, "price", datevalue (today () – 365)), 2.2) – 1