Importing Excel Data

[crisrich]

I'm using FM in Brazil and here it's often not possible to download financial infomation directly from the Internet. Specially for Investment Funds. It seems that they don't like to publish their data in a transparent manner and it can only be obtained by copying from a table generated by our SEC equivalent. From there I can paste them into Excel.

Now I can't find a way to transfer the data from Excel to FM. None of the 6 available format options in the Generic Import command seem to apply. I only have values on the date and the closing price. From your Help document I know that I have to generate a file with the following header:

DD/MM/YYYY,NAV

In what format do I save it? Which import format do I choose?

[Mark]

Hi crisrich,

Notice that in the import dialog, you can enter your own format string. You don't have to choose one of the 6 listed there. Just type your own, right in there. The 6 listed are just the 6 most recent ones used (or defaults if you've never imported).

So, in Excel just save to a Comma Separated Values (*.CSV) file, and then type in your format string when doing the import. You don't need the format string to be in the importing file, you only have to type it into the import dialog.

Also, it is a good idea to take a look at your saved CSV file in Notepad or some text editor, so you can see the actual contents. Viewing a CSV file in Excel can hide things like double quotes, etc...

[crisrich]

Hello Mark,

Thank you very much! It worked . Very easy if you know the trick.

I wasn't aware that I could type in my own format string right into the select field. Am I the only one who did't get that, you think? I saw another post about converting an Excel file to OFX first, with a lot of details to be fixed. Wouldn't it have been easyer to also just save the file as CSV and then describe the format in the Generic Import dialogue box?

By the way: why is the file exported by Excel called CSV if the values ares separated by semicolons and not commas?

Thanks again,
Cristiano

[Mark]

Hi Cristiano,

Glad that worked. You're not the first one to not notice you could type in your own format string... I don't recall the other post exactly, but yes, creating your own OFX file is not trivial. CSV files are quite a bit simpler. The CSV file saved from Excel should have the fields delimited with a comma (not a semicolon). CSV stands for "Comma Separated Values".

[taillje]

Hello,
Why can't FM take right away files from Excel format?
I understand very well the conversion to CSV and it works but with 20 or so funds I would like to follow, it is taking quite a bit of time to download every file (excel mode is download format) then convert them to CSV and then Price Import Manual Generic (ctrl+E) for every file. This whole process takes me an hour and it is very repetitious.

bye taillje

[Mark]

Hi taillje,

Native Excel files are .XLS files, have a binary format, support multiple sheets, and aren't as generically compatible as CSV files. It could be possible to support .XLS files directly, but CSV files are more generic, and can be easily created from Excel.

Importing prices from CSV files isn't normally something you have to do on a regular basis. You can just retrieve prices from our supported quote servers. Are you tracking some investments that aren't available on any of our quote servers?

See: http://www.fundmanagersoftware.com/intrtv.html