Mrrrr's Forum (VIEW ONLY)
Un forum care ofera solutii pentru unele probleme legate in general de PC. Pe langa solutii, aici puteti gasi si alte lucruri interesante // A forum that offers solutions to some PC related issues. Besides these, here you can find more interesting stuff.
|
Lista Forumurilor Pe Tematici
|
Mrrrr's Forum (VIEW ONLY) | Reguli | Inregistrare | Login
POZE MRRRR'S FORUM (VIEW ONLY)
Nu sunteti logat.
|
Nou pe simpatie:
crazy_hell_angel pe Simpatie
 | Femeie
25 ani
Gorj
cauta Barbat
25 - 53 ani |
|
Mrrrr
AdMiN
 Inregistrat: acum 17 ani
Postari: 2228
|
|
I need to have the current exchange rates of the National Romanian Bank in an excel file.
This can be done by getting the rates from an XML file on the bank's website, then for ease to refresh the data connection to have the current rates when opening the excel file.
Everything I tried to make the refresh automatic when opening the file has failed, except for a Workbook_Open VBA snippet. I enabled everything in Trust Center Settings - External Content, I even added the location to Trusted Locations. Nothing of that worked. Disabled all of them, then added the short snippet from 2. below to ThisWorkbook and saved the Excel file as macro enabled.
1. Add bank exchange rates to an Excel file
1. Create a new macro-enabled excel file, for example New.xlsm
2. Go to DATA ribbon and click the From Web button (should be 3rd from the left)
3. Open and the following page should automatically load:
4. Copy the 2nd link above and paste it in the URL field in Excel and click OK
Note: if a window appears with several options on the left and a Connect button on the bottom right, just press the button
5. In the next window select Cursul de schimb on the left side of the window, and on the right side you should see the table with several columns - mine has 4.
My important data is shown in Column1 and Column2, containing:
Column1 .............. Column2
1 EUR ............. 49261
1 USD ............. 40356
The shown data is not correct since 1 EUR is 4,9261 and 1 USD is 4,0356. To edit that, in the window I must click on the Transform Data button to open Power Query Editor.
6. In the right side of the Power Query Editor window there is a box with Applied steps, last one being Changed Type with an X on its right side. Click that to remove Changed Type.
7. Click on Column2 on the ABC letters then select Using Locale. Under Data Type select Decimal Number and under Locale select English (United States), then click OK.
Now the data in the columns should be shown correctly.
8. Select Column3 and Column4 and click the Delete key to remove them - if they're not needed.
9. On the top left side of Power Query Editor window click on Close & Load - Close & Load To...
10. In the box that appears leave Table selected and either leave New worksheet selected or select a cell in the current worksheet, then click OK.
The table with the current (today's) exchange rates for EUR and USD appears.
You can refresh this table manually by right clicking it and clicking Refresh, or ... see below:
2. Refresh the date on file open
1. Right click on the sheet and choose View Code
2. In the top left of the window find your file (eg. VBAProject (New.xlsm)) and double click ThisWorkbook
3. Copy and paste the following code in ThisWorkbook:
Sub RefreshData()
ActiveWorkbook.RefreshAll
' or you can give specific workbook and worksheet identification
End Sub
Private Sub Workbook_Open()
' Call Refresh Data Subroutine when opening file
RefreshData
End Sub
|
4. Open File - Options - Trust Center - Trust Center Settings - External Content - Enable All Data Connections (not recommended).
Otherwise it will show a yellow band when you open the file saying that Data Connections are disabled.
5. To have proof that the file is updating automatically, either remember the exchange rate or type it in a cell outside the table. Tomorrow when you open the file, compare the value you typed in the cell with the value in the table to confirm that it's refreshing automatically on file open.
Sources:
_______________________________________
|
|
| pus acum 3 ani |
|
Mrrrr
AdMiN
Inregistrat: acum 17 ani
Postari: 2228
|
|
If you want all the exchange rates for today or for the current year instead of just the 2 on the main page of the bank, you can use one of the following 2 links:
Today rates for all currency:
Current year (2021) rates for all currency:
Now in order to show all columns in the resulting table with data, after loading the link you will have to choose a table to load, pick the Body table and then the Transform Data button to go into Power Query Editor (PQE).
In PQE you get 3 columns: Subject, OrigCurrency, Cube with values on 1 row: Referance rates, RON and Table.
Now the Table value in the Cube column is an expandable value containing more columns. Don't click it cause if you do it will remove the current columns and load 2 other: Rate and Attribute:Date, Rate having Table values.
So you didn't click the Table value in Cube column, instead there is a button on the right side of the Cube word that looks like to opposing arrows - click that, then click OK. This will add the 2 columns of Cube table to your existing ones, thus you will end up having 4 columns: Subject, OrigCurrency, Cube.Rate and Cube.Attribute:date.
Cube.Rate also has Table as values, again click the opposing arrows to the right of the Cube.Rate value, then OK to add even more columns.
You can change the the Cube.Attribute:date type to Date so it shows 04.01.2021 instead of 2021-01-04.
You can change the Cube.Rate.Element:Text type to Using Locale - Data Type = Decimal Number - Locale = English (United States) to show them as numbers with decimals separated by , (comma) instead of . (point).
Then you can click Close & Load - Close & Load To... and get the entire table since January or the table with all currencies of today.
Since you have all the year's history but want to extract only today's value for 1 currency, let's say EUR, you will have to do the following steps:
1. Select the entire table of currencies, then go to INSERT ribbon - PivotTable - OK
2. Select
- Cube.Rate.Element:Text ............... as Rows,
- Cube.Rate.Attribute:currency ............... as Rows
- Cube.Attribute:date ............... as Values
3. Click on Sum of Cube.Attribute:date and choose Value Field Settings and select Max, then OK
4. Make sure the date column is properly formatted - as date, eg. 25.05.2021.
5. Click on the first currency (eg. AED) then under Row Labels click on the arrow down and from the drop down list select EUR
6. Right click a value in the EUR column, then select Filter - Top 10
7. Options should be:
Show = Top, 1, Items by Max of Cube.Attribute:date
Then click OK.
8. You can show your PivotTable in tabular form in PivotTable Tools - Design - Report Layout - Show in tabular form
9. You can remove all totals in PivotTable Tools - Design - Grand Totals - Off for rows and columns, and Do not show subtotals.
Now your PivotTable should look like:
EUR ........ 4,9261 ........ 24.05.2021
Note: if you add the VBA codes from No 2 from the post above, the date from 9. will update automatically as well as the entire table of currencies for the current year.
_______________________________________
|
|
| pus acum 3 ani |
|