Need help to overcome Cycle of Dependencies - Investment Calculator app

Ask for Help
1

Hi everyone,

so I am stuck with this problem of Cyclic of Dependencies. I want to create an app where I put an ‘Opening Balance’ of a day. Then Investors put some Capital In or Withdraw their Capital. Through that I calculate their Closing Balance and the percentage of each balance.

Now for example we start with 1st January 2023 which is the very first day of trade.

January 1st 2023 - Opening Balance is $ 0

3 Investors come in and invest their capital:
Mark: $500
James: $1000
John: $1500

Total investment for 1st January is: $3000.

That means:
Marks share = 16.67%
James share = 33.33%
John share = 50%

Their closing balance is:
Mark: $500
James: $1000
John: $1500

January 1st 2023 closing balance will also be: $3000.

Now on 14 February I update the balances.
I check the balance and found out that last times $3000 has become $6000 in the trade. So I open my sheet and update the Opening Balance of 14 February to $6000.

Now what should happen is:
based on the % of share from last trade, I should be able to calculate the investors opening balance.
In this case:

I know from 1st January 2023 my investors closing share % was:
Marks share = 16.67%
James share = 33.33%
John share = 50%

So on 14th February when I input the new opening balance of $6000, the opening balance of each investor should be as following:
Mark: 16.67% * $6000 = $1000
James: 33.33% * $6000 = $2000
John: 50% * 6000 = $3000

I hope I am making sense :stuck_out_tongue: but if I am not feel free to look at the attached spreadsheet to have a better idea.

The only thing that is stopping me to achieve this on glide is Cyclic Dependencies which totally makes sense. Just can’t get my head over it.

Cyclic dependencies
Cyclic dependencies1551×509 195 KB

tradingsheet
tradingsheet786×727 37.9 KB

The only manual entries in the app should be:

  1. Opening balances of the day
  2. Capital in by investors
  3. Capital out by investors

Any help would be deeply appreciated.

2

Hi - did you ever find a workaround for this? I am struggling with a similar issue with circular dependencies.

2 Likes
3

To fix the circular dependency error, you need to check your flow of data from column to column.

Ultimately you are trying to use ColumnA in ColumnB which is then used somewhere along the line to determine the value in ColumnA. If columnA is required to calculate ColumnB, and ColumnB is used to calculate a value in ColumnA, then you’ve created a circle which would end up in a never-ending loop.

The answer is to study your logic and find out where you have created the circle.

4

Thing I don’t understand is i can get it to work in GSheets without the circular dependency issue but not in Glide. Maybe I am missing something. I will start another thread on this, would be great to get your input @Jeff_Hager!

5

Google Sheets can support circular dependency to an extent. Glide doesn’t support that at all.

All in all, you would have to check your flow to see why it happens.

2 Likes
6

@Hugh_van_Niekerk ,

If you still need any help with this then feel free to schedule a call with me here: Calendly - We Don't Code.

1 Like
7

So I see (roughly) where the issue is coming in, but I just don’t know how to get around it. It is pretty crucial to what I am trying to build so hoping it isn’t a complete blocker. You can see the GSheet logic below which doesn’t create an issue.

Col B, C, D, F: Hardcoded
ColE: =if(E5=$E$5, 0, SUM($H$5:H5))
ColG: =if(G5=$G$5, 0,E6F6)
ColH: =$C6$I6/$C$3
ColI: =($D6+$G6)

In your experience are there workarounds in Glide?

8

Can you share a screenshot from glide where you see the error?

9

image
image1930×422 41.7 KB
- the data view

image
image676×576 23 KB
- the error message I get now when I try adjust the ‘Growth Col’

10

So Column E depends on Column H.

Column H depends on Column I.

Column I depends on Column G.

But the column G depends on column E, so there’s your circular.

11

Yup, spot on! But the calc works 100% fine in GSheets. Are you aware of any way around this in Glide?

12

I think the problem is that in your google sheet you are performing a calculation across 2 rows, so there is no actual circular dependency. In glide, calculations happen within the same single row. What you are doing in glide doesn’t match what you are doing in the google sheet.

13

Yup, I feared that might be the case. And is there a way of getting around that which you have seen?

Below is the structure for the GSheet for reference.

image
image1566×190 17.3 KB

1 Like
14

I don’t know enough about your data or what your goal is, but can you restructure your data so everything you need to perform a calculation is all on the same row as separate columns instead of multiple rows with different values shared in the same column but different rows? Otherwise, you may need some single value columns, or relations/lookups to retrieve values from other rows, and then change your calculations to use the single value columns or lookups as well.

3 Likes
15

Thanks for the help everyone, appreciated. Will have a think about how I can refactor things to get around this.

3 Likes
16

Hi @Hassan_Nadeem & @Hugh_van_Niekerk —

I’m the creator of a low/no-code modeling platform called Summit which handles financial models and circular dependencies through simulation, and can return the data as JSON that Glide can consume. Summit also solves for idempotency, unlike Google Sheets, so multiple concurrent users is no problem.

I’m looking for Glide users to test with—so this could be a win-win-win.

Shoot me a DM if you’re interested. I’d be happy to try to solve this with you. First step would be sharing your Google Sheet so I can verify the ability to replicate it in Summit.