Exchange Rates in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2572564%22%20slang%3D%22en-US%22%3EExchange%20Rates%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2572564%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I'm%20trying%20to%20build%20a%20data%20table%20with%20multiple%20exchange%20rates%20for%20a%20couple%20of%20multiple%20dates%20(The%20close%20price%20of%20the%20exchange%20rate%20for%20the%20last%203%20days%20of%20the%20last%205%20years).%20I'm%20trying%20using%20the%20new%20STOCKHISTORY%20function%20but%20I%20have%20not%20been%20able%20to%20get%20it.%20Attached%20you%20will%20find%20an%20example%20of%20the%20table%20I%20am%20trying%20to%20build.%20If%20you%20have%20other%20ideas%2C%20it%20would%20be%20awesome!%20Thank%20you!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2572564%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2573767%22%20slang%3D%22en-US%22%3ERe%3A%20Exchange%20Rates%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2573767%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1109496%22%20target%3D%22_blank%22%3E%40elatorre70%3C%2FA%3E%26nbsp%3BPerhaps%20the%20attached%20file%20can%20get%20you%20started.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-07-22%20at%2007.02.30.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F297528i373127F7D24B0EBE%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-07-22%20at%2007.02.30.png%22%20alt%3D%22Screenshot%202021-07-22%20at%2007.02.30.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EThe%20double%20FILTER%20function%20is%20needed%20to%20get%20rid%20of%20the%20row%20and%20column%20headers%20that%20are%20created%20by%20the%20STOCKHISTORY%20function.%20Couldn't%20think%20of%20any%20other%20way%20to%20do%20that.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2573982%22%20slang%3D%22en-US%22%3ERe%3A%20Exchange%20Rates%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2573982%22%20slang%3D%22en-US%22%3ESTOCKHISTORY%205th%20parameter%20(%5BHeader%5D%2C%20default%3D1%20for%20Show%20headers)%20can%20be%20set%20to%200%20(no%20headers).%20So%20formula%20in%20B2%20could%20be%3A%3CBR%20%2F%3E%3DIFERROR(STOCKHISTORY(%24A2%2CB%241%2CB%241%2C%2C0%2C1)%2C%20%22-%22)%3C%2FLINGO-BODY%3E
New Contributor

Hello, I'm trying to build a data table with multiple exchange rates for a couple of multiple dates (The close price of the exchange rate for the last 3 days of the last 5 years). I'm trying using the new STOCKHISTORY function but I have not been able to get it. Attached you will find an example of the table I am trying to build. If you have other ideas, it would be awesome! Thank you!!

5 Replies

@elatorre70 Perhaps the attached file can get you started.

Screenshot 2021-07-22 at 07.02.30.png

The double FILTER function is needed to get rid of the row and column headers that are created by the STOCKHISTORY function. Couldn't think of any other way to do that.

 

STOCKHISTORY 5th parameter ([Header], default=1 for Show headers) can be set to 0 (no headers). So formula in B2 could be:
=IFERROR(STOCKHISTORY($A2,B$1,B$1,,0,1), "-")

@Riny_van_Eekelen 

Just in case, for some cells recalculation gives

image.png

I guess due to errors are inside. Didn't try to fix it since @L z. formula works.

@L z. Okay! Didn't know that as I actually never use that function.

@Riny_van_Eekelen. Played with it only recently after wasting time on Power Query returns only 100 rows