Serving the Quantitative Finance Community

 
Michael_E
Topic Author
Posts: 58
Joined: July 11th, 2016, 12:49 am

Quickest way to parse this data into CSV for input?

January 28th, 2017, 10:39 am

I have a productivity problem, where I am trying to take all this un-formatted text, say dow 30 stocks with 20 years of returns,
and  I want to click copy and paste into say, EXCEL. However, it just pastes the text into 1 column and many rows with lots of spaces.

My overall goal is to export it as a TAB delimited file or CSV to import into another editor, say to wrap it up into a R zoo or xts matrix format with date as the index, so I can actually multiply matrices.   

OBVIOUSLY --- one work around is to use QUANDL or QUANTMOD to import Yahoo Finance data directly into R by fetching or scraping the website.   The problem is, I want this data to be the EXACT data in this particularly enormous data set I'm working with, which is particular and not on yahoo finance or quandl. 

The only workaround so-far I've found is to paste all the data into NOTEPAD and just manually put a comma or tab at each number and row column.   It's incredibly slow and unproductive, it's a time killer and I would really be grateful for a better way to work around this problem from some of the quant community. 

Thanks
 
User avatar
Traden4Alpha
Posts: 3300
Joined: September 20th, 2002, 8:30 pm

Re: Quickest way to parse this data into CSV for input?

January 28th, 2017, 1:09 pm

A couple of options:

1. global find-replace in a text editor
2. create a small spreadsheet that uses the FIND() and LEFT() functions to progressively extract each column from the block of pasted text
 
User avatar
Hansi
Posts: 41
Joined: January 25th, 2010, 11:47 am

Re: Quickest way to parse this data into CSV for input?

January 28th, 2017, 1:22 pm

More specific, this is what you want to use if using Excel: https://support.office.com/en-us/articl ... 3e9c363ed7

If you want to use a text editor you can use Notepad++ or similar and search for regular expression tags (spaces, tabs etc).
 
Michael_E
Topic Author
Posts: 58
Joined: July 11th, 2016, 12:49 am

Re: Quickest way to parse this data into CSV for input?

January 28th, 2017, 3:38 pm

Excellent!   Thank you gents!
 
User avatar
kermittfrog
Posts: 23
Joined: September 9th, 2010, 10:25 am
Location: Frankfurt

Re: Quickest way to parse this data into CSV for input?

January 31st, 2017, 6:55 am

A somewhat more involved option is to write the corresponding [schema.ini] file (https://msdn.microsoft.com/en-us/librar ... s.85).aspx) and connect to the file via the text file driver. Then, simply "connect" to the file using an ODBC connection.

Added bonus: You can directly execute SQL statements on that file.
 
User avatar
snufkin
Posts: 64
Joined: January 25th, 2017, 9:05 am
Location: Cambridge

Re: Quickest way to parse this data into CSV for input?

January 31st, 2017, 5:54 pm

It's not exactly clear what is the original dataset's format and what is the final goal. If the original data is some kind of text, and the final goal is to get it into R, I would stick to R — it has everything one may need to slice and dice a file, _provided it fits in the memory._ As an added bonus, you will be able to import the dataset again would it be updated.

Maybe it makes some sense to share a piece of the original data and the description of what you're looking for?
 
User avatar
DominicConnor
Posts: 41
Joined: July 14th, 2002, 3:00 am

Re: Quickest way to parse this data into CSV for input?

March 12th, 2017, 5:08 pm

I think that a mix of sed and grep will do this, certainly I've used them that way.

If we had a sample, it would be easier to help.