Using online/live data in calculation to calculate value in multiple currencies

1. What is the issue? Please be detailed.
So I've been thinking about creating a form that would calculate a total currency amount, but I would like to display that amount in several different currencies to the user/let the user pick their currency to display. If I were to design this form today, I would put the currencies along with their exchange rates in a CSV and use a pulldata() function to calculate the total amount in different currencies. My question is, for something like this that changes frequently, one could use entities to create a form where an admin could re-enter exchange rates on a regular basis. Is there any way that this could be automated to not require manual input to change the values?

In Google Sheets for example the function =GOOGLEFINANCE("CURRENCY:USD/EUR") pulls live data and updates every few minutes. I wouldn't need it to update that frequently, but daily would be nice.

Based on what I currently know, my guess is that someone would suggest creating an external CSV referenced in the form by a pulldata() function and writing a script that would replace the CSV every so frequently with updated values. I wouldn't know how to do this, but it seems simple right...? :winking_face_with_tongue: Not sure if there are other easier solutions...

I've an even more sophisticated solution - one that may be complex, but it brings some undeniable advantages to the table. :grin:

Why it's worth it:

Perk 01: You get access to highly accurate, real-time data - consistently and reliably.
Perk 02: It's a one-time setup. Once it's deployed, it can run smoothly for years with zero manual intervention.

The approach:

  • Build an external app and make it return the data to ODK Collect.

Time investment? Just 2 - 4 hours if vibe-coded using some AI..! :wink: (it's a simple app, AI shouldn't go wrong with this one..)

Long-term payoff? Massive...!

What about automatically updating an external selects CSV from a Google sheet that had this =Google finance() function?

I haven't done much on the Central backend, but once setup, it looks like it would work indefinitely without creating issues. Here's potentially how that could work:

I forgot to mention that this would be a public web form, so the external app wouldn't actually be a great solution.

Instead of a CSV form attachment, you can use Entities.

From a form design perspective, they are nearly identical, but if you use an Entity List, then you can associate that List with multiple forms, you don't need to update any forms when you update the List, and the API to update a List is easier to work with.

https://docs.getodk.org/entities-intro/#what-s-the-difference-between-entities-and-csv-form-attachments has more on this.

1 Like