Help

Re: New Column Showing Highest Number seen from another cell calculated by a formula

Solved
Jump to Solution
2573 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Chris_Finck
6 - Interface Innovator
6 - Interface Innovator

Im running a stock return spreadsheet and have zapier updating the current price on one column. I then calculate the difference that I bought the stock at vs the current price. I want to add another column that watches for the highest return I have had and the percent return. Is there an if statement that can be watching the price difference and only change the cell if its higher than the previous high I’ve had?

Example. I buy a stock for $10 its now at $15. The net profit is $5. My highest return column shows $5. Tomorrow the stock goes to $20, the highest return column now shows $10 because its higher than the $5.

1 Solution

Accepted Solutions

So in @Justin_Barrett’s excellent example, the “Highest Return” formula depends on the “Highest Price” Rollup in comparison to the “Purchase Price”.

You are wanting another formula, “Current Return” related to “Purchase Price”, which means you need another Rollup for the “Current Return” formula to depend on. However, this rollup will require a few preliminary steps to set it up.

You’ll need a Rollup field in your TICKER table to rollup all of the “Stock Values” records, but instead of looking at the “Value” field and returning MAX(values), you will point it at the “Date” field and return MAX(values) [let’s call this field “Max Date”]. Now you’ve got the most recent Date that a value was fetched for that Stock.

Now go back to your “Stock Values” table – we will create a Lookup field here called “Max Date” that looks at the “Stock” linked record field (which is coming from the TICKER table), and returns the “Max Date” field from that table. Now every record in the “Stock Values” table knows what the most recent date that a value for stock of that type was fetched.

So now we can create another formula field that says “if the date of this record matches the date of the most recent value fetch for this stock, display the value fetched here as the current value of the stock”.
Let’s call this field “Current Value?”, and give it this formula:

IF(
   IS_SAME(
      Date,
      {Max Date},
      'day'  <-- compares the date values to an accuracy of a day; adjust to 'hour' if multiple values are fetched in a single day
   ),
   {Current Price}
)

Format this field as currency; it will be blank for all records except the most recently fetched value for each type of stock, and for that most recently fetched stock of each type, this field will show the current value.

So the final step here is to create that Rollup in the TICKER table that your “Current Return” formula will be based on. Let’s call this Rollup field “Current Value”, point it at the “Stock Values” linked record field, the “Current Value?” table, and use the SUM(values) rollup function. Format as currency. Since there is only 1 value (the most recent value) for any given stock in this rollup, it will always return {Current Value} + 0 + 0 + 0... ie, only the Current Value.
(note: if you are getting more than one value returned for the same stock in this rollup, and thus getting an inflated “Current Value” since more than one value fetched is being summed here, then adjust that accuracy of ‘day’ in the IS_SAME() formula to be more precise – maybe ‘hour’ or ‘minute’, depending on how often you fetch values)

Now you can create your “Current Return” formula, comparing “Purchase Price” to “Current Value”:

{Current Value} - {Purchase Price}

formatted as currency and allowing negative numbers.

You can hide all the superfluous fields that were needed to get here, and only show your final comparison formula fields, if all the others are distracting/messy.

Hope that helps!

See Solution in Thread

11 Replies 11
Mike_McLaughlin
8 - Airtable Astronomer
8 - Airtable Astronomer

you probably don’t want to add a Field (column) every time you make trade.

I’d suggest create a table of Ticker symbols, and another table for Trades where you link the ticker to a trade record.

have a drop down in the Trades table for buy or sell, and a helper field that looks at the value of the trade and buy/sell column and turns the value into a + or - value.

in the Ticker table, use a Rollup with a sum function on the helper field in Trades to get the net gain/loss.

I’m skipping logic to account for a varying number of shares, assuming for a simplified scenario that you are always going completely in/out of a position.

I agree with Mike’s suggestion about using a [Ticker] table for tracking each stock. To spin off of that, I suggest doing the highest-return calculation on that table using a Rollup field.

I made a [Stock Values] table for stock tracking, with the {Stock} field linking to a record in the [Ticker] table. The primary field combines the date and stock name for easy reference (if you want to use it) elsewhere.

24%20PM

Over on the [Ticker] table, I have a rollup field to find the highest price for that stock from the [Stock Values] table, using the MAX(values) aggregation function, and a simple formula field to calculate the highest return. With a lone stock value, it looks like this:

52%20PM

Adding another record for that stock…

34%20PM

…will update the prices in the [Ticker] table, as long as it’s actually a higher value:

29%20PM

If the stock price drops the next day, the highest value won’t change:

36%20PM
29%20PM

Is this what you’re looking for?

@Justin_Barrett So I tried to replicate it. I also want to be able to show what the most recent price input is returning in addition to the highest one I have had.

The trouble I am running into is that It wont calculate the current return $ or % from a “linked” price

So in @Justin_Barrett’s excellent example, the “Highest Return” formula depends on the “Highest Price” Rollup in comparison to the “Purchase Price”.

You are wanting another formula, “Current Return” related to “Purchase Price”, which means you need another Rollup for the “Current Return” formula to depend on. However, this rollup will require a few preliminary steps to set it up.

You’ll need a Rollup field in your TICKER table to rollup all of the “Stock Values” records, but instead of looking at the “Value” field and returning MAX(values), you will point it at the “Date” field and return MAX(values) [let’s call this field “Max Date”]. Now you’ve got the most recent Date that a value was fetched for that Stock.

Now go back to your “Stock Values” table – we will create a Lookup field here called “Max Date” that looks at the “Stock” linked record field (which is coming from the TICKER table), and returns the “Max Date” field from that table. Now every record in the “Stock Values” table knows what the most recent date that a value for stock of that type was fetched.

So now we can create another formula field that says “if the date of this record matches the date of the most recent value fetch for this stock, display the value fetched here as the current value of the stock”.
Let’s call this field “Current Value?”, and give it this formula:

IF(
   IS_SAME(
      Date,
      {Max Date},
      'day'  <-- compares the date values to an accuracy of a day; adjust to 'hour' if multiple values are fetched in a single day
   ),
   {Current Price}
)

Format this field as currency; it will be blank for all records except the most recently fetched value for each type of stock, and for that most recently fetched stock of each type, this field will show the current value.

So the final step here is to create that Rollup in the TICKER table that your “Current Return” formula will be based on. Let’s call this Rollup field “Current Value”, point it at the “Stock Values” linked record field, the “Current Value?” table, and use the SUM(values) rollup function. Format as currency. Since there is only 1 value (the most recent value) for any given stock in this rollup, it will always return {Current Value} + 0 + 0 + 0... ie, only the Current Value.
(note: if you are getting more than one value returned for the same stock in this rollup, and thus getting an inflated “Current Value” since more than one value fetched is being summed here, then adjust that accuracy of ‘day’ in the IS_SAME() formula to be more precise – maybe ‘hour’ or ‘minute’, depending on how often you fetch values)

Now you can create your “Current Return” formula, comparing “Purchase Price” to “Current Value”:

{Current Value} - {Purchase Price}

formatted as currency and allowing negative numbers.

You can hide all the superfluous fields that were needed to get here, and only show your final comparison formula fields, if all the others are distracting/messy.

Hope that helps!

Chris_Finck
6 - Interface Innovator
6 - Interface Innovator

Airtable is lucky to have such a great community willing to help others. Thank you all, seriously. I feel like if I posted this on reddit I would get downvotes and a “just go look it up on the internet” hahaha.

So this will fix most of the problem. The last question is that right now, I am currently sorting both my airtable and google sheets by the name in order and then copying and pasting the updated ticket price each day. I was having zapier do it, but the multi zap only allows instant, which is too much in my opinion.

I’m not sure I understand what the question is here.

Are you looking for a way to have zapier do this for you? (you can have zapier interact directly with AT, you know? could cut out the google sheets middleman…)

I need google sheets to pull =googlefinance for updated numbers. But Zapier only allows for instant on multizaps which uses up my 1000 zaps within a day lol.

The only thing that comes to mind right now is that you could use zapier’s “Push” button trigger to handle transferring rows from Google sheets to records in Airtable in bulk on a daily, or hourly basis, or however often you want to “push” that button…

Thats a good idea and I will look into it. With your and Justins tutorial I was able to create the table. Last question. How would I show the date associated with the highest return? i.e. I want to know what day the highest return was on in the “ticker” table

For that, you’d create a Lookup in your “Stock Values” table that look at the TICKER Table linked record, and the “Highest Price” field — let’s call this field “Max Value”.

Doing that pulls the “Highest Price” (the MAX(value) of all the prices) into the “Stock Values” table, so that every Stock Value record knows what the Highest Price of it’s associated Stock type was at any point in time. Since that each record for a given type of stock now knows what it’s highest ever value was, you can tell it to compare its own value to the highest ever in a new formula field, and if it matches (ie, that row recognizes itself to be the highest value for that stock), return the Date for that record (the date for the highest value) — let’s call this one “Max Value Date?”:

IF(
   Value = {Max Value},
   Date
)

Now only those stocks of a given type that contain the highest value will return their dates into this field - note, it could be more than one…

Back in your TICKER table, we will create a Rollup field, call it “Date of Highest Price” — point it at the “Stock Values” table, and the “Max Value Date” field, and use the MAX(values) rollup function to ensure you get the most recent of all the dates that this stock hit that highest value.

Jeremy, Thank you for all of the help. You answered every single question I had. I am now understanding the table logic as well and how to bring certain data sets over from one another.

Cheers!