CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulastockgoogle-financeback-testing
nachiket jadhav
nachiket jadhav

Reputation: 1

Use Google finance to check when a stock hits a target price in a given period

I am trying to do stock backtesting in Google sheets. I have entered scrip symbol in A3, entry date in B3, the last date to exit the stock in C3 and the target price in D3. Now I want to find on which date my stock hits its target price of D3 in between the dates of B3 and C3.

I used the Google finance historical price function =googlefinance(a3,""all",c3,d3). I get an array of rows mentioning all dates between C3 and D3 along with their Open high low close values. Now I want to search on which date does my target price of D3 lies between the high and low values.

I thought of using Vlookup or Match functions but they aren't fitting for my problem. Any other function that I can use?

Upvotes: 0

Views: 117

Answers (1)

Babanana
Babanana

Reputation: 1476

Other Methods of Choosing Data

I would suggest using Filter or Query. I do think that in your case Filter is best.

Your Formula Results to Something like this:

I created a sample data based on a result from Google Finance this case, the tickler is "NASDAQ:GOOG"

Sample Data

Data Set

Formula to Use:

You can try this:

=FILTER(E2:J21,G2:G21>D3,H2:H21<D3)

The result would look something like this: Result of the Formula

If you are looking to keep the Date Column only,you can use Choosecols function.

=CHOOSECOLS(FILTER(E2:J21,G2:G21>D3,H2:H21<D3),1)

Reference:

Filter

Upvotes: 0

Related Questions