Update excel spreadsheet

[MFG1988]

Hi. Just want to use csv file to update column in excel spreadsheet?

[#2]

@MFG1988 And what is your question?

[#3]

@Riny_van_EekelenSorry if not clear. I have excel spreadsheet with share details and want to apply a csv file containing updated share prices. It seems fairly basic but I can find no good explanation in microsoft help.

[#4]

@MFG1988 You can import data from a CSV file via Data, "From Text/CSV" (Get & Transform Data). Alternatively, you can try opening the CSV directly in Excel. Most likely, Excel will split the csv data into columns for you. If not, you need to use Data, "Text to columns".

If you can upload a sample of the "master" file (delete any private and confidential info) and a csv file you would like to use for updating, it will be easier to find a solution.

[#5]

@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

[#6 · Accepted solution]

@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!

[#7]

Hi Riny. Success!! Thanks again for all your help. How do I recognise your contribution in Tech Community?

@Riny_van_Eekelen