Pull exchange data from another workbook

M

Maniac189

New Member
Joined
Jun 13, 2024
Messages
8
Office Version
  1. 365
Platform
  1. Windows
Good Morning Everyone. I hope you are well. I am a pretty novice excel user. I do basic book-keeping. I have a new client that does a lot of foreign currency transactions that need to be converted to CAD, the problem is that the bank downloads do not include the exchange rate when it is a EUR or USD account (because it is only working in those currencies). So I use a file from the Bank of Canada for exchange rates. At present i need to manually enter the data from the Bank of Canada file and it then the formula converts it to CAD. I am worried about making an error... so.....to my question...

Is there a way to pull the data from another workbook (i can even paste it into the current workbook if that is easier), so that on X date it pulls the exchange rate from the BOC list?

IE on Jan 4th 2024 the conversion rate was 1.34456 so pull that rate into the current sheet so i don't have to manually enter it?

Thanks for your help. I added a small image if it helps. I tried uploading the mini sheet but it didn't work.

Michael.
 

Attachments

  • excel fx rate.png
    excel fx rate.png
    30.4 KB · Views: 2

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
No dates in BoC file for the value errors , so what needs too happen there?

Book1
ABCDE
1USDFXRateCAD
218/01/2024182.031.3505110.7815
308/02/2024260.611.346781.62349
417/03/202439.59#VALUE!
531/07/202448.681.380911.98621
618/08/2024554.84#VALUE!
728/09/202468.22#VALUE!
Sheet1
Cell Formulas
RangeFormula
D2:D7D2=XLOOKUP(Sheet1!A2:A7,csv_start_date_2017_01_03[date],csv_start_date_2017_01_03[FXUSDCAD],"",0,1)
E2:E7E2=(C2*D2)
Dynamic array formulas.
Book1
ABC
1dateFXEURCADFXUSDCAD
202/01/20241.45841.3316
303/01/20241.45771.3356
404/01/20241.46231.3354
505/01/20241.46141.3347
608/01/20241.46571.3372
709/01/20241.46391.3391
810/01/20241.46641.3382
911/01/20241.46941.3409
1012/01/20241.46731.3387
1115/01/20241.4711.3436
1216/01/20241.46631.3483
1317/01/20241.46911.3522
1418/01/20241.46691.3505
1519/01/20241.46541.3462
1622/01/20241.4651.3454
1723/01/20241.46171.3478
1824/01/20241.46981.3484
1925/01/20241.46371.3496
2026/01/20241.4611.3449
2129/01/20241.45361.3442
2230/01/20241.45451.3417
2331/01/20241.45321.3397
2401/02/20241.45421.3404
2502/02/20241.45271.3454
2605/02/20241.45261.3527
2706/02/20241.45121.3506
2807/02/20241.45061.3469
2908/02/20241.45011.3467
3009/02/20241.45121.3458
3112/02/20241.4491.345
3213/02/20241.45281.3556
3314/02/20241.45311.355
3415/02/20241.45261.3493
3516/02/20241.45211.3484
3620/02/20241.46191.3518
3721/02/20241.4611.3512
3822/02/20241.461.3494
3923/02/20241.46071.3494
4026/02/20241.46611.3515
4127/02/20241.46671.3521
4228/02/20241.47041.3574
4329/02/20241.4681.357
4401/03/20241.4691.3564
4504/03/20241.47341.3572
4605/03/20241.47451.3582
4706/03/20241.4741.3528
4807/03/20241.47211.3474
4908/03/20241.47441.3471
5011/03/20241.4741.3491
5112/03/20241.47391.3499
5213/03/20241.47461.3472
5314/03/20241.47271.3512
5415/03/20241.47351.3533
5518/03/20241.47341.3541
5619/03/20241.47471.3581
5720/03/20241.47281.3559
5821/03/20241.47061.3525
5922/03/20241.471.3593
6025/03/20241.47181.3583
6126/03/20241.47111.3572
6227/03/20241.47011.3587
6328/03/20241.46321.355
6401/04/20241.45921.3574
6502/04/20241.46091.3572
6603/04/20241.46421.3537
6704/04/20241.46661.3504
6805/04/20241.47231.3598
6908/04/20241.47361.3582
7009/04/20241.47481.3578
7110/04/20241.47031.3671
7211/04/20241.46841.3692
7312/04/20241.46481.3766
7415/04/20241.46381.3764
7516/04/20241.4691.3821
7617/04/20241.46921.3794
7718/04/20241.46651.3764
7819/04/20241.46531.3748
7922/04/20241.46011.3715
8023/04/20241.46231.3673
8124/04/20241.46591.3709
8225/04/20241.46711.3689
8326/04/20241.46241.3668
8429/04/20241.46361.3658
8530/04/20241.46951.3746
8601/05/20241.471.3759
8702/05/20241.46671.3698
8803/05/20241.47241.3672
8906/05/20241.47231.3661
9007/05/20241.47571.3705
9108/05/20241.4761.3734
9209/05/20241.47481.3692
9310/05/20241.47221.3665
9413/05/20241.4761.3672
9514/05/20241.47671.3653
9615/05/20241.47921.3615
9716/05/20241.48021.362
9817/05/20241.47931.3615
9921/05/20241.48161.3649
10022/05/20241.48141.3674
10123/05/20241.48361.3702
10224/05/20241.48341.3673
10327/05/20241.48041.3636
10428/05/20241.48251.3638
10529/05/20241.48241.37
10630/05/20241.4821.3678
10731/05/20241.48021.3637
10803/06/20241.48311.3635
10904/06/20241.4881.3681
11005/06/20241.48891.3696
11106/06/20241.48971.3686
11207/06/20241.4861.374
11310/06/20241.481.3765
11411/06/20241.47781.3767
11512/06/20241.48361.37
11613/06/20241.47921.3746
11714/06/20241.47021.3751
11817/06/20241.47351.3743
11918/06/20241.47341.3723
12019/06/20241.47321.3709
12120/06/20241.46781.3698
12221/06/20241.4651.3702
12324/06/20241.46631.3663
12425/06/20241.46261.3659
12526/06/20241.4631.3696
12627/06/20241.46611.369
12728/06/20241.46591.3687
12802/07/20241.47051.3697
12903/07/20241.47131.3637
13004/07/20241.47131.3613
13105/07/20241.47631.3633
13208/07/20241.47711.3634
13309/07/20241.47481.3638
13410/07/20241.47431.362
13511/07/20241.48111.3624
13612/07/20241.48561.3628
13715/07/20241.49021.3664
13816/07/20241.491.3686
13917/07/20241.49641.3685
14018/07/20241.49441.3696
14119/07/20241.49421.3727
14222/07/20241.49721.3755
14323/07/20241.49431.3768
14424/07/20241.49671.3794
14525/07/20241.49971.3819
14626/07/20241.50191.383
14729/07/20241.49861.3852
14830/07/20241.49741.3851
14931/07/20241.49491.3809
15001/08/20241.49431.3846
15102/08/20241.51111.3858
15206/08/20241.50691.3794
15307/08/20241.50081.3739
15408/08/20241.49911.3743
15509/08/20241.49961.3732
15612/08/20241.50121.3738
15713/08/20241.50471.3723
15814/08/20241.51121.3711
15915/08/20241.50561.3715
16016/08/20241.50811.3707
16119/08/20241.51041.3651
16220/08/20241.51391.3627
16321/08/20241.51411.3592
16422/08/20241.51181.3602
16523/08/20241.51071.3529
16626/08/20241.50521.3481
16727/08/20241.50351.346
16828/08/20241.49751.3471
16929/08/20241.49281.3474
17030/08/20241.49211.3491
17103/09/20241.49591.3548
17204/09/20241.49751.3525
17305/09/20241.49991.3514
17406/09/20241.50221.3545
17509/09/20241.49771.3561
17610/09/20241.49931.3599
17711/09/20241.49731.3589
17812/09/20241.50091.3591
17913/09/20241.5061.3586
18016/09/20241.51211.3593
18117/09/20241.5121.3596
18218/09/20241.51231.359
18319/09/20241.51191.3563
18420/09/20241.51391.3565
18523/09/20241.50291.351
18624/09/20241.50221.3468
18725/09/20241.50231.3462
18826/09/20241.50461.3474
18927/09/20241.50761.3499
19001/10/20241.49471.3504
19102/10/20241.49051.3491
19203/10/20241.49331.354
19304/10/20241.48951.3577
19407/10/20241.49391.3609
19508/10/20241.49881.3657
csv_start_date=2017-01-03
 
Upvote 0
Solution
Kerryx said:
No dates in BoC file for the value errors , so what needs too happen there?

Book1
ABCDE
1USDFXRateCAD
218/01/2024182.031.3505110.7815
308/02/2024260.611.346781.62349
417/03/202439.59#VALUE!
531/07/202448.681.380911.98621
618/08/2024554.84#VALUE!
728/09/202468.22#VALUE!
Sheet1
Cell Formulas
RangeFormula
D2:D7D2=XLOOKUP(Sheet1!A2:A7,csv_start_date_2017_01_03[date],csv_start_date_2017_01_03[FXUSDCAD],"",0,1)
E2:E7E2=(C2*D2)
Dynamic array formulas.
Book1
ABC
1dateFXEURCADFXUSDCAD
202/01/20241.45841.3316
303/01/20241.45771.3356
404/01/20241.46231.3354
505/01/20241.46141.3347
608/01/20241.46571.3372
709/01/20241.46391.3391
810/01/20241.46641.3382
911/01/20241.46941.3409
1012/01/20241.46731.3387
1115/01/20241.4711.3436
1216/01/20241.46631.3483
1317/01/20241.46911.3522
1418/01/20241.46691.3505
1519/01/20241.46541.3462
1622/01/20241.4651.3454
1723/01/20241.46171.3478
1824/01/20241.46981.3484
1925/01/20241.46371.3496
2026/01/20241.4611.3449
2129/01/20241.45361.3442
2230/01/20241.45451.3417
2331/01/20241.45321.3397
2401/02/20241.45421.3404
2502/02/20241.45271.3454
2605/02/20241.45261.3527
2706/02/20241.45121.3506
2807/02/20241.45061.3469
2908/02/20241.45011.3467
3009/02/20241.45121.3458
3112/02/20241.4491.345
3213/02/20241.45281.3556
3314/02/20241.45311.355
3415/02/20241.45261.3493
3516/02/20241.45211.3484
3620/02/20241.46191.3518
3721/02/20241.4611.3512
3822/02/20241.461.3494
3923/02/20241.46071.3494
4026/02/20241.46611.3515
4127/02/20241.46671.3521
4228/02/20241.47041.3574
4329/02/20241.4681.357
4401/03/20241.4691.3564
4504/03/20241.47341.3572
4605/03/20241.47451.3582
4706/03/20241.4741.3528
4807/03/20241.47211.3474
4908/03/20241.47441.3471
5011/03/20241.4741.3491
5112/03/20241.47391.3499
5213/03/20241.47461.3472
5314/03/20241.47271.3512
5415/03/20241.47351.3533
5518/03/20241.47341.3541
5619/03/20241.47471.3581
5720/03/20241.47281.3559
5821/03/20241.47061.3525
5922/03/20241.471.3593
6025/03/20241.47181.3583
6126/03/20241.47111.3572
6227/03/20241.47011.3587
6328/03/20241.46321.355
6401/04/20241.45921.3574
6502/04/20241.46091.3572
6603/04/20241.46421.3537
6704/04/20241.46661.3504
6805/04/20241.47231.3598
6908/04/20241.47361.3582
7009/04/20241.47481.3578
7110/04/20241.47031.3671
7211/04/20241.46841.3692
7312/04/20241.46481.3766
7415/04/20241.46381.3764
7516/04/20241.4691.3821
7617/04/20241.46921.3794
7718/04/20241.46651.3764
7819/04/20241.46531.3748
7922/04/20241.46011.3715
8023/04/20241.46231.3673
8124/04/20241.46591.3709
8225/04/20241.46711.3689
8326/04/20241.46241.3668
8429/04/20241.46361.3658
8530/04/20241.46951.3746
8601/05/20241.471.3759
8702/05/20241.46671.3698
8803/05/20241.47241.3672
8906/05/20241.47231.3661
9007/05/20241.47571.3705
9108/05/20241.4761.3734
9209/05/20241.47481.3692
9310/05/20241.47221.3665
9413/05/20241.4761.3672
9514/05/20241.47671.3653
9615/05/20241.47921.3615
9716/05/20241.48021.362
9817/05/20241.47931.3615
9921/05/20241.48161.3649
10022/05/20241.48141.3674
10123/05/20241.48361.3702
10224/05/20241.48341.3673
10327/05/20241.48041.3636
10428/05/20241.48251.3638
10529/05/20241.48241.37
10630/05/20241.4821.3678
10731/05/20241.48021.3637
10803/06/20241.48311.3635
10904/06/20241.4881.3681
11005/06/20241.48891.3696
11106/06/20241.48971.3686
11207/06/20241.4861.374
11310/06/20241.481.3765
11411/06/20241.47781.3767
11512/06/20241.48361.37
11613/06/20241.47921.3746
11714/06/20241.47021.3751
11817/06/20241.47351.3743
11918/06/20241.47341.3723
12019/06/20241.47321.3709
12120/06/20241.46781.3698
12221/06/20241.4651.3702
12324/06/20241.46631.3663
12425/06/20241.46261.3659
12526/06/20241.4631.3696
12627/06/20241.46611.369
12728/06/20241.46591.3687
12802/07/20241.47051.3697
12903/07/20241.47131.3637
13004/07/20241.47131.3613
13105/07/20241.47631.3633
13208/07/20241.47711.3634
13309/07/20241.47481.3638
13410/07/20241.47431.362
13511/07/20241.48111.3624
13612/07/20241.48561.3628
13715/07/20241.49021.3664
13816/07/20241.491.3686
13917/07/20241.49641.3685
14018/07/20241.49441.3696
14119/07/20241.49421.3727
14222/07/20241.49721.3755
14323/07/20241.49431.3768
14424/07/20241.49671.3794
14525/07/20241.49971.3819
14626/07/20241.50191.383
14729/07/20241.49861.3852
14830/07/20241.49741.3851
14931/07/20241.49491.3809
15001/08/20241.49431.3846
15102/08/20241.51111.3858
15206/08/20241.50691.3794
15307/08/20241.50081.3739
15408/08/20241.49911.3743
15509/08/20241.49961.3732
15612/08/20241.50121.3738
15713/08/20241.50471.3723
15814/08/20241.51121.3711
15915/08/20241.50561.3715
16016/08/20241.50811.3707
16119/08/20241.51041.3651
16220/08/20241.51391.3627
16321/08/20241.51411.3592
16422/08/20241.51181.3602
16523/08/20241.51071.3529
16626/08/20241.50521.3481
16727/08/20241.50351.346
16828/08/20241.49751.3471
16929/08/20241.49281.3474
17030/08/20241.49211.3491
17103/09/20241.49591.3548
17204/09/20241.49751.3525
17305/09/20241.49991.3514
17406/09/20241.50221.3545
17509/09/20241.49771.3561
17610/09/20241.49931.3599
17711/09/20241.49731.3589
17812/09/20241.50091.3591
17913/09/20241.5061.3586
18016/09/20241.51211.3593
18117/09/20241.5121.3596
18218/09/20241.51231.359
18319/09/20241.51191.3563
18420/09/20241.51391.3565
18523/09/20241.50291.351
18624/09/20241.50221.3468
18725/09/20241.50231.3462
18826/09/20241.50461.3474
18927/09/20241.50761.3499
19001/10/20241.49471.3504
19102/10/20241.49051.3491
19203/10/20241.49331.354
19304/10/20241.48951.3577
19407/10/20241.49391.3609
19508/10/20241.49881.3657
csv_start_date=2017-01-03
Click to expand...
Thank you very much for this, i will play around and try and figure it out. If i need additional support, i will come back.

The people here are just awesome!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Return Last But One Value from a range with specific conditions
Replies
3
Views
383
Fluff
Fluff
A
  • Question Question
Need to convert value depending on currency and date
Replies
2
Views
467
deLockloire
D
A
  • Question Question
Excel - looking up data with multiple criteria from table/array, date must be same or nearest before the target cell
Replies
8
Views
557
Peter_SSs
Peter_SSs
N
  • Question Question
VBA to add title from formula to a save as name
Replies
0
Views
95
NKUNZI
N
J
  • Solved
Data pulled from another spreadsheet not refreshing
Replies
1
Views
223
Joe4
Joe4

Forum statistics

Threads
1,223,849
Messages
6,175,005
Members
452,600
Latest member
nicoCrous75

Share this page

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