Master Feed into two other tabs

[DeeDee95]

Hi Guys,

This is my first post here, so I hope I am following the correct rules for this, if not please do let me know!

I have the following issue that I am unsure on how to resolve:

Master feed - This tab connects to the Dynamics 365 data entity which displays FX rates when refreshed each month. The rates contained in this tab are the Default Rates, Reporting rates, and average rates for only one currency exchange combination (EUR-GBP) and not for all the rest.
Month End rate - This tab should contain only the rate at the last day of the month for each currency exchange combination. This should be taken from the above mentioned Master feed.
Monthly Average Rate - This tab should contain the average rate from all the default rates inserted in each day of the month.

They look like this:

MONTH END RATE
FY20
From To Current Month Prior Month Prior Year Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19

GBP EUR 1.1234 1.1027 1.1227 1.16125 1.15841 1.13404 1.11499 1.0923 1.1027 1.1234 1.1602
GBP USD 1.2292 1.2177 1.3030 1.30405 1.29211 1.26303 1.26952 1.2175 1.2177 1.2292 1.2894
GBP AUD 1.8187 1.8105 1.8038 1.83848 1.83257 1.82338 1.80798 1.7684 1.8105 1.8187 1.8789
GBP CAD 1.6280 1.6198 1.6827 1.74081 1.74059 1.70435 1.66617 1.6038 1.6198 1.6280 1.6873
GBP CHF 1.2181 1.2044 1.2794 1.29731 1.31782 1.27477 1.23968 1.2058 1.2044 1.2181 1.2794
GBP CZK 29.0298 28.5571 28.2910 30.01494 29.75858 29.30149 28.38209 28.0230 28.5571 29.0298 29.5972
GBP DKK 8.3874 8.2225 8.3744 8.67702 8.64853 8.46975 8.33225 8.1558 8.2225 8.3874 8.6678
GBP INR 86.8407 87.0360 94.5082 90.53032 90.23853 88.28755 87.52538 83.8111 87.0360 86.8407 91.4108
GBP JPY 132.7164 129.5496 147.9429 144.56395 144.40710 138.56574 136.97111 132.2261 129.5496 132.7164 140.3482
GBP KRW 1,477.8508 1,471.2229 - 1482.50221 1499.46508 1503.79809 1437.2246 1471.2229 1477.8508 1504.7913
GBP NOK 11.1524 11.0688 10.6177 11.24981 11.22382 11.06384 10.83015 10.6356 11.0688 11.1524 11.8789
GBP SEK 12.0397 11.9523 11.5881 12.10515 12.28562 12.06083 11.78559 11.6255 11.9523 12.0397 12.5253

EUR USD 1.0942 1.1042 1.1607 1.12297 1.11542 1.11374 1.13859 1.11462 1.10422 1.0942 1.11135
EUR AUD 1.6189 1.6418 1.6068 1.58319 1.58197 1.60785 1.62152 1.61892 1.64182 1.6189 1.61945
EUR CAD 1.4492 1.4675 1.4988 1.49908 1.50258 1.50289 1.49434 1.46818 1.46747 1.4492 1.45429
EUR CHF 1.0843 1.0922 1.1396 1.11717 1.13762 1.12410 1.11183 1.10385 1.09218 1.0843 1.10276
EUR CZK 25.8414 25.8967 25.7614 25.84714 25.68924 25.83806 25.45493 25.65409 25.89673 25.8414 25.51022
EUR DKK 7.4662 7.4565 7.4595 7.47215 7.46589 7.46863 7.47291 7.46634 7.45651 7.4662 7.47093
EUR INR 77.3028 78.9278 84.1832 77.95950 77.89884 77.85199 78.49854 76.72607 78.92779 77.3028 78.78828
EUR JPY 118.1398 117.4808 131.7801 124.49014 124.66012 122.18731 122.84472 121.04838 117.48078 118.1398 120.96813
EUR KRW 1,315.5352 1,334.1646 - 1,276.64550 1,294.42037 1,326.04958 1,315.72850 1,334.16456 1,315.5352 1,297.00119
EUR NOK 9.9276 10.0376 9.4577 9.68769 9.68902 9.75610 9.71319 9.73655 10.03764 9.9276 10.23857
EUR SEK 10.7174 10.8388 10.3221 10.42426 10.60562 10.63524 10.57009 10.64274 10.83883 10.7174 10.79573


MONTHLY AVERAGE RATE
FY20
From To Current Month Prior Month Prior Year Mar-19 Apr-19 May-19 Jun-19 Jul-19 Aug-19 Sep-19 Oct-19 Nov-19

GBP EUR 1.1204 1.0910 1.1190 1.1650 1.1593 1.1502 1.1227 1.1133 1.0910 1.1204
GBP USD - - -
GBP AUD - - -
GBP CAD - - -
GBP CHF - - -
GBP CZK - - -
GBP DKK - - -
GBP INR - - -
GBP JPY - - -
GBP KRW - - -
GBP NOK - - -
GBP SEK - - -

EUR USD - - -
EUR AUD - - -
EUR CAD - - -
EUR CHF - - -
EUR CZK - - -
EUR DKK - - -
EUR INR - - -
EUR JPY - - -
EUR KRW - - -
EUR NOK - - -
EUR SEK - - -



MASTER FEED

From currency Exchange rate type To currency Start date Conversion factor Exchange rate Expiration Name
GBP Default USD 2018-02-01 1 1.4237 2018-02-27 Default global rate
GBP Default INR 2018-02-01 1 91.1168 2018-02-27 Default global rate
GBP Default EUR 2018-02-28 1 1.1299 2018-02-28 Default global rate
GBP Default INR 2018-02-28 1 89.8594 2018-02-28 Default global rate


The Master feed being it connected to Dynamics, I cannot modify it without it loosing its connection to Dynamics and therefore not update the rates each month automatically.

Essentially we need the last rate of the month for each currency combination in the Month End rate tab and the average of each currency combination on the monthly average rate tab.

This will have to auto update each time I update the master feed.

I hope this makes sense and someone can help in any way! Would be extremely appreciated.

Thanks a lot.
Diana

[The_Snook]

Hi DeeDee95,

Welcome to the forum! It's always easier for people to provide solutions if you upload an example of the workbook/data you are working with (see yellow banner above for guidance on how to upload workbooks).

Regards,

Snook

[DeeDee95]

Hi Snook,

Thank you very much, and apologies I am still getting used to the Forum.

Have I now attached a file that you are able to view at all?

Thanks,
Diana

[The_Snook]

Unfortunately not. Try:

1. Click 'Edit post'.
2. Scroll down to 'Manage Attachments'.
3. Select 'Choose file' and browse to the required file and click ok.
4. Click the 'Upload' button.
5. Select 'Close Window'.
6. Click the 'Save Changes' button.