I have a series of dates down the page and the next cell is a stock price (pulled with an API token)
12/10/2021 =IF(B331=TODAY(),WEBSERVICE(CONCATENATE("https://cloud.iexapis.com/stable/stock/",$C$11,"/quote/latestPrice?token=",Crypto!J3)),C331)
12/11/2021 =IF(B332=TODAY(),WEBSERVICE(CONCATENATE("https://cloud.iexapis.com/stable/stock/",$C$11,"/quote/latestPrice?token=",Crypto!J3)),C332)
12/12/2021 =IF(B333=TODAY(),WEBSERVICE(CONCATENATE("https://cloud.iexapis.com/stable/stock/",$C$11,"/quote/latestPrice?token=",Crypto!J3)),C333)
12/13/2021 =IF(B334=TODAY(),WEBSERVICE(CONCATENATE("https://cloud.iexapis.com/stable/stock/",$C$11,"/quote/latestPrice?token=",Crypto!J3)),C334)
etc
I want to insert a formula that if the date is today() it will perform the formula, but if it isn't (past or future date) it will leave the cell data alone.
Basically, I want to have it automatically fill in the stock price for the date, but then I want it to keep the historical data from previous dates after that date has passed.
I have included the formula to capture the data (stock prices) for each date, but when i try previous dates, it changes the values to 0.
Can anyone help with what I'm doing wrong to keep the historical data?
12/10/2021 =IF(B331=TODAY(),WEBSERVICE(CONCATENATE("https://cloud.iexapis.com/stable/stock/",$C$11,"/quote/latestPrice?token=",Crypto!J3)),C331)
12/11/2021 =IF(B332=TODAY(),WEBSERVICE(CONCATENATE("https://cloud.iexapis.com/stable/stock/",$C$11,"/quote/latestPrice?token=",Crypto!J3)),C332)
12/12/2021 =IF(B333=TODAY(),WEBSERVICE(CONCATENATE("https://cloud.iexapis.com/stable/stock/",$C$11,"/quote/latestPrice?token=",Crypto!J3)),C333)
12/13/2021 =IF(B334=TODAY(),WEBSERVICE(CONCATENATE("https://cloud.iexapis.com/stable/stock/",$C$11,"/quote/latestPrice?token=",Crypto!J3)),C334)
etc
I want to insert a formula that if the date is today() it will perform the formula, but if it isn't (past or future date) it will leave the cell data alone.
Basically, I want to have it automatically fill in the stock price for the date, but then I want it to keep the historical data from previous dates after that date has passed.
I have included the formula to capture the data (stock prices) for each date, but when i try previous dates, it changes the values to 0.
Can anyone help with what I'm doing wrong to keep the historical data?