Converting US data to EU data in one operation through VBA?

[Elijah]

Hi all,

I'm a Norwegian resident interested in obtaining daily data of the S&P 500 index. This can be done for free through Yahoo Finance:

S&P 500 Daily Data.


The problem is that the US seems to use different separators, both between columns and for decimals (comma "," in EU and "." in US). The result is that when I download this data, all is contained in one column.

A solution for this is to:

1) Use the "Text to column" function to split the data into columns;

2) Use the "Seek and replace" to switch from "." to ",".

This is of course a cumbersome process on a daily basis.

Sadly, I have no experience with VBA, but I was wondering if it's possible to create a script that does this all in one go? I could then have a sheet with raw data directly from Yahoo and a second sheet with converted data.

Or maybe there is a smarter solution?

Back in the day I had the same problem and changed my system settings from EU to US. However, I have a looooot of sheets now in EU format, so it's not really a workable solution.

Thanks in advance for any help.

Elijah

[alansidman]

Hi and welcome to the forum

Pictures are of little value. Honestly, no one wants to re-type your data to try and solve your issue. Additionally, we would only be guessing at how your data was structured, ie. formulas, formatting, etc. Additionally, due to how some browsers behave, many of our members cannot see uploaded pictures/images. Please do not take this route.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" of what you wish the output to be.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[Elijah]

Hi, Alan,

I appreciate the comment.

I have had some great help on this forum in the past and I always make it a point to attach Excel files. The reason I didn't do it this time is because I suspected US residents will get the sheet correctly displayed instead.

I am now uploading a sheet where all the steps I take are detailed in separate sheets:

1. Raw data *.csv is downloaded from Yahoo through this link.

2. I use the function "Text to columns" to convert the data from one column to 7 columns as it should be. This is done for me by choosing "Comma" as separator.

3. Seek and replace all "." with ",". By the way, my Excel often tends to freeze/crash when doing this if the data set is large.

4. Finally, format my data to numbers with two decimals.


I did attach pictures on all the sheets to show how they look on my computer. They're pasted in on the right side of each sheet.

Finally, I had to save the data in another format than *.csv since it had multiple sheets, so I saved it in original Excel format.


In summary, my problem is that I can obtain the data easily, BUT, I need to go through many steps to get it in European format. Since this is a process I'd like to do on a daily basis, I'd love it if there were a simpler solution.
Preferrably, I'd find a data provider in Europe, but I don't know anyone who supplies this for free...

Thanks in advance.

[Elijah]

It seems like I forgot to add the files yesterday!

The original file was too large to upload, so I zipped it. If zip is a problem, I did make a smaller version deleting a few thousand rows of data to make it smaller. Also removed the pictures...

[kvsrinivasamurthy]

Here is the macro code.
ARRAY(1,4) is for DMY for date. Depending upon requirement chnge 4 to other number.

Please Login or Register  to view this content.

[nilem]

or maybe so

Please Login or Register  to view this content.

[jindon]

After download, don't open csv via Excel.

Run the code from the fresh workbook and let's see how it goes.

Please Login or Register  to view this content.

[Elijah]

Wow! Thank a lot, guys! Truly appreciate the effort.

All the code ran well (it's the first time I've used VBA, so I had to google a little to understand how to proceed), but only nilem's code accomplished what I wanted (except formatting with two decimals at the end).

kvsrinivasamurthy - separates the data into columns, but it's still "." as separator.

jindon - uses semicolon as separator

[Elijah]

nilem,

Not sure if it's possible without too much trouble, but would it be possible to output the data with two decimals only?

I.e., 2686,1001 = 2686,10

Not sure why there's so many decimals anyway? Maybe an additional error due to US/EU conversion issues in format?

The norm for this data is two decimals as can be seen here:

https://finance.yahoo.com/quote/%5Egspc?ltr=1

[jindon]

jindon - uses semicolon as separator

Please Login or Register  to view this content.

[nilem]

...Not sure why there's so many decimals anyway?...

Look at the CSV file, there are 6 decimal places
Date,Open,High,Low,Close,Adj Close,Volume
1950-01-03,16.660000,16.660000,16.660000,16.660000,16.660000,1260000
1950-01-04,16.850000,16.850000,16.850000,16.850000,16.850000,1890000

[Zer0Cool]

Instead of doing find/replace you should be able to just change the format/region.

Also I presume you are opening the csv directly into Excel (by double clicking it). You may instead open Excel first and use the text import wizard (basically Text to columns) which may let you both split up the columns and format in 1 movement.

Of course if you do this very frequently a macro cant hurt to help speed it up as others have offered.

[Elijah]

Look at the CSV file, there are 6 decimal places
Date,Open,High,Low,Close,Adj Close,Volume
1950-01-03,16.660000,16.660000,16.660000,16.660000,16.660000,1260000
1950-01-04,16.850000,16.850000,16.850000,16.850000,16.850000,1890000

Yes. I was wondering if perhaps it's an error due to format/region when I'm downloading it...

Instead of doing find/replace you should be able to just change the format/region.

Also I presume you are opening the csv directly into Excel (by double clicking it). You may instead open Excel first and use the text import wizard (basically Text to columns) which may let you both split up the columns and format in 1 movement.

Of course if you do this very frequently a macro cant hurt to help speed it up as others have offered.

Yes, but that's in my Excel settings, right? Wouldn't that render my sheets made in my current region obsolete? I'm pretty sure that happened the last time I attempted this.

The text import wizard is pretty neat. I wasn't aware of that function. Still, I don't seem to be able to replace "." with "," using that one.

EDIT: It does seem like I can replace the "." with "," in the wizard, but it does involve editing.

So, the VBA from nilem seems to be my best bet for now. I can simply format the data to numbers with two decimals and have saved some time for sure.

Thanks again for all help! Much appreciated!

[Elijah]

[code]

Appreciate it, jindon, but it seems like I'm still getting some errors on this one...

[Elijah]

Hi again, everybody,

I think the data I initially posted may be OK, but I discovered somewhere else that delivers data that I know for sure is better.

The problem is that this data is in *.txt format.

I tried using the Text Import Wizard suggested by Zer0Cool and by several cumbersome steps, I AM able to get everything right:

1. Open Wizard

2. Select Edit

3. Change Date

4. Change "." to "," on the numbers (not date)

5. Change the numbers from text to decimal numbers

6. Finally import to Excel

7. Format as numbers with two decimals in Excel

I'd hate to ask since I already got help with the *.csv, but it would of course be great if this could be done using a macro similar to what I already got already.

I'm attaching both the Excel (with picture embedded in case conversion makes it look different on your computer) of how it should look like and the raw data in *.txt in case anyone should feel up for it.

Thanks.

[jindon]

For the txt file uploaded.

Please Login or Register  to view this content.

[nilem]

or maybe with minor changes

Please Login or Register  to view this content.

[Elijah]

Hey guys,

Really appreciate the effort, but I'm sad to say that neither worked 100%. Actually, it was Jindon's which were closest.

The numbers were correct, although they weren't formatted as numbers with two decimals. That's not a big deal. I can fix it myself...

But the date was a little off from the regional format here. This means that when formatting this as a table, Excel doesn't recognize it as a date which makes sorting hard.

I'm attaching a picture of how it looks on my screen and how I envisioned it side by side.

Thanks again.

[jindon]

Then how about

Please Login or Register  to view this content.

Edit: Added lines for decimal separator to a comma.

[Elijah]

Great! Thanks a lot, Jindon!

Works very well with the dates.

Finally, the numbers are now formatted as "Standard". Some have two decimals. Some have 1 decimal. Some have zero decimals.

For me, it's a couple of clicks to format the data into numbers with two decimals, so either way I'm happy.

Or do you have any ideas on how to integrate that in the macro?

If not, no worries! I'll marked this as solved anyway.

[jindon]

See if this rounds up to 2 decimals...

Please Login or Register  to view this content.

[Elijah]

Yes, Jindon, it does! Fantastic!

Let me know if you're in Norway. I'm buying beer!

[jindon]

You are welcome and thanks for the rep.

OK, thanks for the offer...

[Elijah]

Hi again,

I started to play around a little with the data.

Finally, I'd like to format this data in a table. I'm attaching a sheet to show how it's done.

Of course, it's not much work to paste the raw data produced by the macro into a table in a separate sheet, BUT, it would be very nice if the macro could insert this
data in row 2 instead.

If so, I imagine that I could simply update the table directly using the macro? And also overwrite older data when updating the *.txt file?

The column with "1s" in F is superfluous also. Not sure what it's doing there really...

Would this be a quick fix in the macro? If so, I'd be very grateful.

[jindon]

Do you want it like this?

Please Login or Register  to view this content.

[Elijah]

Absolutely great, Jindon!

Would there be a way to not include column F with the "1s" when making the conversion?

PS: Wasn't able to give you more reps.

[jindon]

Please Login or Register  to view this content.

The code only reads first 5 columns from text file.
Do you want all 6 columns?

[Elijah]

Hi, Jindon,

Sorry, my bad. It works exactly as it should already. I only want the first 5 columns. I was very tired this morning, so must have messed up something.

Thanks again.

A final question from me, although I hate to ask for more help...

Is it possible to import the data from the *.txt file displaying the newest dates on top, i.e., from newest to old? The opposite of how it's currently displayed?

I figured I could switch this fast myself using the sort function, BUT, the problem is that doing this seems to disrupt some of the formulas I'm making in additional columns.

If not, no worries of course. But it would be so nice if this is possible as I've gotten a great worksheet now that's fast to use thanks to your help.

[jindon]

If you want it sorted by Date then change to

Please Login or Register  to view this content.

[Elijah]

Hi, Jindon,

I just tried it now very fast and it appears to work great! Thanks again! Really appreciated.