Real-time currency conversion spreadsheet design help

[skylinekiller]

I am in the design phase and looking to get some ideas on the best way to design my spreadsheet.
We have a small private org that makes purchases and hold fundraisers. Some items we buy in Japanese yen and some we buy in US Dollars. We also receive Japanese Yen and US dollars. What is the best way to get an automated formula with the currency exchange rate the date it is getting entered? I thought about importing an object from the web and referencing the exchange rate table, but when I can't find a way only to refresh the newly inputted information.


This is my current design. I want it to pull realtime exchange rate with target currency US Dollars, but I only want real-time currency the day its entered. The Balance I want is in US Dollars

A1, B1, C1, D1, E1, F1, G1, H1, I1
Date, Items purchased, Event, Dollars, Yen, Ex Rate, Total US Dollars, Total Japanese Yen, Balance


Any help or input on my real-time currency design dilemma is greatly appreciated.

 

[steve the fish]

You can do that relatively simply. You will need to create a table of Yen ex rates with a date component. Each transaction has a date so you then query the table based on the date to find the ex rate.

 

[skylinekiller]

Can you elaborate on the date component? I'm not sure what you are referring to.