Forum Discussion
Update excel spreadsheet
MFG1988 Best done by using "Get&Transform Data" (a.k.a. Power Query "PQ"). Are you familiar with that? If not, this is a great place to start:
https://exceloffthegrid.com/power-query-introduction/
It takes some learning but that's worth it.
You can collect all your CSV files in one folder. Connect to that folder and pick-up the latest CSV file. Then, keep columns 1 (Code) and 4 (Last $), promote headers and load back to an Excel table.
That will result in a table with two columns. One for the Code and one for the Last $ value.
Now you need to add a column with the proper code for each security in your Master list. Then, you can use VLOOKUP (or the more modern XLOOKUP) to find the Last $ for each security in the table that was created from the CSV file.
Once set-up, that could run automatically. Just save a more recent CSV file into the designated folder, go to the Master and press Data, Refresh All.
Have attached your Master file containing a PQ solution that will connect to a CSV only. So, not to a folder. And the link in the Source will not work as I don't know where you keep your files, so I just made up a file path and name before attaching it here. But you can test if if you edit the Source so that it can find one of your own CSV's.
Let me know if you need more help!
Riny_van_EekelenHi Riny. I have supplied sample masterfile (holdings_05102020) and the csv (holdings_02-10-2020)
What I wish to do is update column F in the master (Last($)) with column D from the csv (also labelled Last $)
Much appreciate your spending time on this - if I can automate it will save a lot of time!
Regards. Mike
MFG1988 Best done by using "Get&Transform Data" (a.k.a. Power Query "PQ"). Are you familiar with that? If not, this is a great place to start:
https://exceloffthegrid.com/power-query-introduction/
It takes some learning but that's worth it.
You can collect all your CSV files in one folder. Connect to that folder and pick-up the latest CSV file. Then, keep columns 1 (Code) and 4 (Last $), promote headers and load back to an Excel table.
That will result in a table with two columns. One for the Code and one for the Last $ value.
Now you need to add a column with the proper code for each security in your Master list. Then, you can use VLOOKUP (or the more modern XLOOKUP) to find the Last $ for each security in the table that was created from the CSV file.
Once set-up, that could run automatically. Just save a more recent CSV file into the designated folder, go to the Master and press Data, Refresh All.
Have attached your Master file containing a PQ solution that will connect to a CSV only. So, not to a folder. And the link in the Source will not work as I don't know where you keep your files, so I just made up a file path and name before attaching it here. But you can test if if you edit the Source so that it can find one of your own CSV's.
Let me know if you need more help!
Hi Riny. Success!! Thanks again for all your help. How do I recognise your contribution in Tech Community?