I need to create and make changes to a list of things (anywhere from 1-120 items) quickly (almost instantly). I have been stuck on this problem for well over a month so I would really appreciate all the help I can get.
I’m building an app that helps retail investors invest in the long run. On the app, users input their past trades – ticker, quantity, and the date. The app will use that data and create a list of things (anywhere from 1-120 items depending on the date). Here is how the workflow works:
User makes a transaction (input past trades): ticker, quantity, date
Triggers a backend workflow
a. Create a list of User earnings for every quarter after the date of the transaction.
b. Update a list of User earnings for every quarter after the date of the transaction that already exists.
Edit:
c. Calculate the sum of User earnings for that transaction
d. Calculate the sum of all User earnings where the user is = current user (this is what users see on the front end and need to see updated real-time following a transaction)
For example, if I make a transaction for Apple’s stock in January 2015, the app will create a database entry for April, July, September, December 2015, April, July, September, December 2016 etc. until April 2022 (today). If entries from 2015 to today already exist, it will make a change to those entries. The further a user goes back, the more entries are created. At most, 120 items will be created/updated following a transaction.
I am using a recursive backend workflow to create the User earnings. Right now it takes up to 2 minutes to create the list. While that may be perfectly normal, it is far too long for a good user experience.
When I built this on Google Sheets, the figures could update in real-time since the cells and their values reference other cells. I understand that Bubble’s database is very different from Google Sheets and Excel. I want to replicate a similar user experience.
I have read other users using the Data API to create ~1000 items in less than a minute. If anyone has any ideas on how to go about this or any other ideas to make this workflow much faster, I would love to hear them.
Unfortunately, Bubble is slow at running through lists on the backend and while I’m sure there are some opportunities to tighten up your workflow, you might like to consider some of the following:
Splitting the workflow to first focus on x most recent quarters and working backwards from there. Users are probably not going to be able to digest 120 quarters of data instantly so starting at the most recent periods first to give them feedback and then adding in the historical data would be my approach.
You can play around with loading animations rather than the user just looking at an empty screen, or think about other structural things you can change in your app so this doesn’t become so much of a bottleneck. i.e. can you have the user do something else as a next step while that is crunching for you in the background? Actually so many apps (even with code) are horribly slow at doing things but they disguise it in smart ways so the user isn’t just staring at the screen waiting.
Josh @ Support Dept
Helping no-code founders get unstuck fast save hours, & ship faster with an expert on-demand
Bubble can run this in seconds, the only problem you’ll have is that it will run too fast, causing errors or skipping over data, never try this client side in less than a minute.
Instead use a loading or progress view for your UX, this should be more than enough to get you going at this stage.
You can also schedule this in the backend, updating the client when ready.
I don’t see any issues here, you just need to take a different approach and make it simple, clients will understand.
@josh24 My apologies I should have clarified this better:
Your first point on splitting the workflow.
The user will see one number / a cumulative graph of a list of User earnings: the sum of all user earnings for all stocks they have ever traded not an actual list of user earnings. This is how it works:
Calculate the sum of user earnings for each unique ticker the user has ever traded. Up to 120 items must be updated almost instantly following the user “submitting” their trade**.
Calculate the sum of all User earnings where the user is = current user (this is what users see on the front end and need to see updated real-time following a transaction).
For example:
I “buy” an Apple share in January 2000. This will create new / update the values of every existing user earnings where the quarter is > January 2000 for that ticker, and user = current user (~88 items)
Calculate the sum of user earnings where user = current user and ticker = AAPL. This is the user earnings of my AAPL shares and the “value” of a single holding.
Calculate the sum of user earnings where the user = current user. This is the total value of my portfolio.
Because the value users care about is the sum of user earnings, I need the (up to) 120 items to be created/updated at the same time.
On your second point on loading animations.
One of the goals of this app is to help people invest in great companies for the long run. They need to be able to see / experiment how making a transaction further back in the past can dramatically change their user earnings and overall returns. Having somewhat of an instant feedback loop is important.
I want this tool to be like a long-term investment calculator where I can experiment by adding historic or hypothetical investments from the past and learn from experimentation. I will experiment with loading animations but a workflow that takes ~2 minutes for every transaction they make is simply too slow.
One of the goals of this app is to help people invest in great companies for the long run. They need to be able to see / experiment how making a transaction further back in the past can dramatically change their user earnings and overall returns. Having somewhat of an instant feedback loop is important. (Like in google sheets where you can change one variable or cell and the whole spreadsheet updates instantly)
I want this tool to be like a long-term investment calculator where users can experiment by adding historic or hypothetical investments from the past and learn from experimentation. I will look into loading animations but a workflow that takes ~2 minutes for every transaction they make is simply too slow.
I think you just need to look really closely at the way you structure your data to make this as efficient as possible. Thinking about it some more perhaps there is a simpler way?
Some comments:
If we are both users of the app and we both bought shares in AAPL 15 years ago, then most of what you are creating is duplicated even if we invested different amounts on different days. Like the price data that you’re pulling back from an API is the same and the relative return is the same. So perhaps you need to look at how you can cache data at the stock level so some of the work is already done for the next user that comes along and has also invested in AAPL. Of course you still need to calculate the returns for each quarter for that user but the effort would be far less. You may also like to look at using third-party APIs like Math.js and seeing whether you could pass off a bunch of data and get it to crunch it for you and then just store it in nested JSON format in one cell on your database. Might be way quicker than going one by one through a loop.
Perhaps moving some of the calculations to the client side (but you’d need to test this to see what happens). i.e. if you had the price data for the last 120 quarters or whatever stored for each stock in the database, then there is a scenario where you do all of your calculations dynamically on the client side without creating too many records in the database. This is kinda what google sheets is doing. But the user experience is going to be dependant heavily on the user’s computer, internet connection etc. Otherwise if you want the server to do all of the crunching your options are going to be slower as you work through a list one-by-one vs. in parallel.
Josh @ Support Dept
Helping no-code founders get unstuck fast save hours, & ship faster with an expert on-demand
I don’t see this to be possible(at least not instantly), aside from building the logic you’ll still run into workflow limitations, caching issues, rate-limiting on the back-end etc.
One approach you might try is by preloading some calculation values in custom states, using them when needed for calculation
If you do find a better way let us know, we’re always eager to learn
A version of this will get you the instant results (assuming that the calculation for each value is not reliant on a 3rd party API call) that you’re looking for but will take you away from vanilla Bubble. You would need a short script to generate the values and then either store them as a list on the db, or as JSON if you need to store other meta data against each value.
@edwardbutcher The calculation for the value is not reliant on a third-party call. All the information needed for the calculations will already be in a database. I have little to no coding experience. Could you elaborate on what you meant by using a short script to generate the values?
If I understand your first point correctly, you’re suggesting that I could use two workflows.
If a user invested in AAPL 15 years ago, when a second user invested in AAPL where the date ≥ the first user’s date I would just copy the list of items from the first user to create the new list and then make changes to the user, date, number of shares, and subsequent calculations instead of having to create everything from scratch?
For the dates that are ≤ first user’s date, I would create new entries up until the earliest date in the database. This user’s entries are now is the “earliest” to invest in AAPL and becomes the new benchmark to copy from. Eventually, the earliest date will keep going back until it can’t anymore and we can use that list as the benchmark permanently.
On your point about using third-party services to process data, I have heard this to be an effective solution. One person suggested that I use a completely different backend like firebase because they can process data much faster. But what you’re suggesting is instead of using a different database entirely, just use it to process data and return the calculated values as raw numbers in a JSON and save it to the database?
I am unfamiliar with using the data API or working with JSON in general so I would really appreciate it if you could point me to an example or resource to get started. If I were to use this method, would I have to save the JSON as a list of items in a single entry?
To your second point, I do and will have all the earnings data for the last 120 quarters. You’re suggesting having input boxes so to speak and a use a math.JS plugin that references those input values and the database to calculate the values. I will experiment with this, but is there any way for Bubble to remember those values when a user refreshes the page? And would I be able to chart these values in a graph?
One thing I am thinking about right now is setting this up as sort of a spreadsheet, and have the inputs autobind to the database while the elements reference the inputs and perform on the fly calculations. Meanwhile I can set up a database trigger workflow and instead of performing calculations on the backend, I just save the values that have been calculated on the front end. I would still have to create a bunch of items but each item should process faster since they’re not doing any searches or performing a calculation.
I wish you all the best, and I mean no disrespect at all, and what you are trying to do is definitely possible in Bubble. But you’re definitely going to need to flex a number of more advanced skills to get there, and particularly to get the performance you desire, and I worry that you may need to brush up on your understanding of a few topics first. I can already see loads of adjacent questions popping up as we try to work through the initial question
Otherwise it’s going to be a long and painful road if you just dive in. I’ve done that a number of times and I wish I could have my sanity back from all those stressful 3am finishes
To be successful with a project like this I think you’re going to need a really strong understand of how to structure databases efficiently (this is #1), backend workflows, strong understanding of the API connector (i.e. you would use this to import price data on the stocks) etc to name a few.
A incorrectly structured database is what will kill you in terms of performance, particularly as you try to onboard users. So I really think you need to focus a lot of effort on mapping this out and nailing it from the get go.
Depending on your budget, I think you could either spend some time getting familiar with some of the fundamentals (there’s great free content on YouTube), but if your budget allows you might also benefit from doing a bootcamp or even bringing in a freelance developer who can help you navigate some of these more tricky topics - there’s plenty of great Bubble developers on the hunt for projects.
Again, I wish you all the best and I know this reply is not answering your questions specifically, but that’s just how I see it. I hope it helps
Josh @ Support Dept
Helping no-code founders get unstuck fast save hours, & ship faster with an expert on-demand
I solved a similar problem with custom states. I stored a large set of data (1500 records) in a custom state, performed calculations on this set that were displayed to the user in a second or so, and only then used a backend worflow to update the database in the background.
Thanks for your candor. I actually took the Build & Launch bootcamp a few months ago which really helped but the more I try to do, the more obstacles I face.
I have actually found somewhat of a workaround using repeating groups (with autobinding for the necessary inputs) to display the data and performing the calculations on there instead of the backend. I get instantaneous results but I know that this is not a sustainable solution.
I am currently learning/trying to figure out how to use list shifter. I’m still a little confused as to how it works exactly, but it seems like it can help achieve what I am trying to do.
You just precisely stated why your problem is a misfit for Bubble. In the language of economic modeling you are trying to simulate historical counterfactuals. In general Bubble is the wrong choice of platform for running the actual simulation…but it is the right choice of platform to build the UX/UI to present the simulation. You will want to offload the simulation computation engine to another platform such as AWS Lambda and then tie back to Bubble.
Hi @themoatapp ! I can`t share the editor due to privacy restrictions, but I made a fast demo and here is the process:
Make a data type with the fileds you need. I just added one field: data (number).
Add a custom state that is a list of variables that you calculate:
Populate this list using a workflow that describes your logic. It will work fast, as it is calculating only in the browser of the user. You can display the result to the user in real-time.
Schedule a backend API with this custom state as a parameter:
save hours, & ship faster with an expert 
on-demand
