Exchange Rates: Get Data from Web with Cell Reference (VBA?)

NagartHUN

New Member
Joined
Mar 21, 2019
Messages
1
Hello guys, first post here on MrExcel.
I'm a proficient Excel user with several years of experience but completely new to VBA and I believe my current problem can only be solved with VBA. I looked the web for hours, but every question was case specific and I couldn't adjust their solutions to my problem.

Context: I'm currently working on a Workbook that helps keep track of your financials. You have the first sheet for data entry (in 1 row: date, place, item, value, currency, category, subcategory, subsubcategory). Then there are other sheets to calculate and display different things.

Problem: On other sheets, when calculating/displaying graphs, since there is a possibility of having/using multiple currencies, displaying e. g. income for all currencies in one month would take up a tremendously large space because you would have to list all currencies (and then comes spending as well, just so it's mirrored). Plus you would have to insert new columns as you own more currencies.

Solution Idea: I believe it would be easier to have a refreshing exchange rate sheet that pulls data from a live table on a website. The one I found is this one. Notice how the link includes the currency (EUR) and the current date (2019-03-21).

Solution Context: What I want to achieve is a table (Data from Web option) with the exchange rates of a currency that is let's say in DifferentSheet.A1 cell and for the date in DifferentSheet.B1, so the table can be rendered and refreshed accordingly to its user's preferences (main/preferred currency to view sum values/graphs) and accordingly to the current date, because the website just has a new link for each new day.
The date to be referenced can be calculated as (suits the format the website uses):
=YEAR(TODAY())&"-"&IF(MONTH(TODAY())<10;0;"")&MONTH(TODAY())&"-"&DAY((TODAY()))

Summary: To sum it up the table would get the data for a given currency (DifferentSheet.A1) and for a given time (DifferentSheet.B1). The table would also refresh by itself in every X mins/opens (I have found the menu for that), and render itself again when the cell values changes mentioned above; namely date or currency referenced (either manually or by the beginning of a new day).

Seen problems: Newly rendered table having the same name as the one it is being rendered onto.

What sort of VBA code would help me overcome this obstacle? Thank you for your help, I really appreciate it!
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top