Home → Problems →

Import Investment data

• Edit

cozmicray

Jan 08, 2011 @ 08:02 PM

I have investment data in a excel spreadsheet
current format (csv save)

DATE,ACTIVITY,DESC,SYMBOL,UNITS,UNITPRICE,FEE,TOTAL
1/4/2010,Buy ,"ALBMP - ALABAMA POWER 6.45% PFD CALLABLE CPN 6.450% FC 12/19/07 CALL1 10/01/17 @ 25.000 010110 2,000 CUSIP-010392488 ",ALBMP,,,,$806.25

I can name the columns anything necessary, and change the order.

I have not sucsessfully been able to convert this to OFX format.

The text import doesn't allow import into investment account.

What can I do to get this data into money dance (mac) (I have PCs and macs, osx.6, Win7, win xp oyhers)

Showing page 2 out of 2. View the first page

1. 31 Posted by diveboss164 on Aug 26, 2011 @ 05:10 PM

   

   Phillip,
   The name is "Retirement Strategies Inc." I hope that helps.

   Kerry

   Sent from my iPhone

   • Edit

2. 32 Posted by pekatete on Aug 26, 2011 @ 06:43 PM

   

   Kerry thanks for getting back with the name.
   I have now written the script for retirement Services (RSI_INC) and you can download it (after downloading the program from the link above) via the program interface. Let me know how you get along, but should be straignt foward.

   @cosmicray -I have updated your script to autogenarate transaction ID's (which are missing from your file. If you could download the script again via the program interface (instructions in previous post), you should then just ensure you check the script name and select Close.

   • Edit

3. 33 Posted by diveboss164 on Aug 27, 2011 @ 08:52 PM

   

   Ok, There was no link unless you were referring to a previous post. I did find a link on a response you made to another MD user. The file is an .EXE file and I am using a MAC OS. Windows programs will not help. If that is all you can write at this time, thanks for trying. If not, and you can write the app for a MAC then that will be great.

   Kerry Vogt
   [email blocked]

   "You tell me, and I forget. You teach me, and I remember. You involve me, and I learn.”
   - Benjamine Franklin

   • Edit

4. 34 Posted by pekatete on Aug 27, 2011 @ 08:57 PM

   

   Kerry - yes I was refering to the link in the previous post. Also YES, the file is for Windows (again like I mentioned in that post). Sorry I can not offer a solution for Mac at the moment but I am working on it and I'll post here when good and ready.

   • Edit

5. 35 Posted by diveboss164 on Aug 27, 2011 @ 09:00 PM

   

   Much appreciated!

   Kerry Vogt
   [email blocked]

   "A pilot lives in a world of perfection, or not at all"
   - Richard S. Drury

   • Edit

6. 36 Posted by Jeffery Ridings on Aug 30, 2011 @ 12:12 AM

   

   Kerry - I found myself needing something similar a couple of days ago, so I whipped out a quick Perl script to convert my csv to QIF. It's not general purpose and isn't debugged yet (e.g. I put Buy but MD says Sell, go figure), but I'd be happy to share. Better than entering data manually. If you've ever done scripting, you'll certainly be able to pick this up and modify it for your own needs. Let me know.
   Regards,
   Jeff

   • Edit

7. 37 Posted by diveboss164 on Aug 31, 2011 @ 01:09 AM

   

   Jeffery, It’s been awhile since I used Perl. I used it around 2001 to write a few scripts for a website I had up. That was back in the Windows day for me. I did find a Perl editor for Mac, so I wouldn’t mind try my had at coding in Perl again. Anything to make importing my csv data into MD possible.

   Kerry Vogt
   [email blocked]

   "You tell me, and I forget. You teach me, and I remember. You involve me, and I learn.”
   - Benjamine Franklin

   • Edit

8. 38 Posted by Jeffery Ridings on Aug 31, 2011 @ 02:47 AM

   

   Kerry,
   You'll have to do a bit of editing on this script, but it'll give you
   a good idea for a start. I had to get quite a few years of investment
   data (and banking data) in a spreadsheet, so I scanned and OCR'd them.
   I finally thought of putting it into Moneydance as part of my
   evaluation. The spreadsheet was of my own creation, so you'll see that
   I dumped almost everything into the description column. You should
   actually have an easier time of it with a well defined csv. You'll run
   it from a command line using STDIN and STDOUT. I suspect I could still
   ride a bike even though it's been well over 10 years, so I think
   you'll pick this back up pretty quick.

   I use the Smultron editor. It's multi-language and has some nice
   features, not the least of which is converting line endings from Mac
   to Mac OSX (Unix LF). If you export a csv from Excel, you'll get
   Windows CRLF and Perl will not behave properly. I ran into that
   problem several times before it became habit to convert the line
   endings. http://smultron.en.softonic.com/mac I've never had a problem
   with this editor and use it for a lot of stuff.

   I don't know how familiar you are with the Terminal or the bash shell,
   so here's a few tips...
   Create a bin folder under your ID and dump the script in there. Edit
   to your liking.
   Open a terminal window. Type in:
   $> export PATH=$PATH:/Users/yourLoginName/bin (no spaces)
   $> cd myDataFolder
   $> cat myData.csv | csv2qif.pl >myData.qif

   A few notes:
   1. Using the Perl "split" function is far and away the easiest method
   to parse a csv.
   2. I split into local variables with the "my" statement. They only
   last inside the loop. Not necessary, but safer and good technique most
   of the time.
   3. I found out that investment data shouldn't be signed, so I had to
   absolute value everything since dollar amounts are signed in my
   spreadsheet.
   4. I typed in the !Type:Invst myself in the qif file.

   Good luck and let me know how it goes, especially if you choose to go
   with OFX instead of QIF. I think their QIF reader is a bit buggy as
   far as memos and stuff like that goes. I have a note into them about
   it although haven't heard anything back yet.

   Regards,
   Jeff

   Jeffery Ridings
   [email blocked]
   http://www.linkedin.com/in/jefferyridings

   • csv2qif.pl 3.59 KB
   • Edit

9. 39 Posted by diveboss164 on Sep 01, 2011 @ 02:58 AM

   

   Thanks, I got the script and will try and get the text editor this weekend after I upgrade my OS to Lion.
   I glanced as the script and see I will have to dust of my brain. I have been building databases in Filemaker Pro
   for so long I forgot how complicated raw scripting can look. I’ll figure it out along with familiarizing myself
   with Terminal. I am new to the MAC world, but Terminal looks like the old DOS prompts so I am sure it’s
   like riding a bike, just a newer model.
   I did figure out the simple cd command after I played with it for a few minutes. My folder name was "CSV2QIF Data”
   and I could not get it to change to that directory. I stripped the “ Data" and made it “CSV2QIF" and then
   tried the cd command again and it worked. Probably needs “@20" in place of the space. I am not sure but would love
   to know the trick.
   Anyway, thanks again and if I get it working I’ll let you know.

   Kerry Vogt
   [email blocked]

   "People who are willing to give up freedom for the sake of short-term security, deserve neither freedom nor security.”
   - Benjamine Franklin

   • Edit

10. 40 Posted by Jeffery Ridings on Sep 01, 2011 @ 03:43 AM

    

    DOS is based a lot on Unix and that's all the terminal is (I've been
    using Unix since the '70's) . The Mac OS shell uses the BASH shell
    (Bourne-Again SHell, a pun and takeoff of the Bourne shell). I
    typically use the Korn Shell at work and it's available on the Mac,
    but I don't bother since I don't do that much shell scripting at home.
    Regardless, using the bash shell, you just need to backslash a space,
    so your command would look like $ cd CSV2QIF\ Data <enter>. The
    backslash tells the shell to take the next character as a literal
    character (as near as I remember).
    As far as Perl goes, I keep a Llama book (Learning Perl from O'Reilly
    Publishing) on my desk at work and one here at home. I rarely do
    anything so complicated that I need their reference book. There's
    plenty of web pages if you don't want to buy a book, too.
    Let me know how it goes with the upgrade to Lion. I'll do that in a
    couple of months.

    Jeffery Ridings
    [email blocked]
    http://www.linkedin.com/in/jefferyridings

    • Edit

11. 41 Posted by diveboss164 on Sep 02, 2011 @ 01:28 AM

    

    THANKS!

    Kerry Vogt
    [email blocked]

    "You tell me, and I forget. You teach me, and I remember. You involve me, and I learn.”
    - Benjamine Franklin

    • Edit

12. System closed this discussion on Mar 31, 2015 @ 03:35 PM.