loadsectrans extension
I installed Mike Bray's extension and tried to load transactions from a cvs file generated by Fidelity. The file contains:
Run Date,Action,Symbol,Security Description,Security Type,Quantity,Price ($),Commission ($),Fees ($),Accrued Interest ($),Amount ($),Settlement Date
07/31/2018, REINVESTMENT, SPAXX, FIDELITY GOVERNMENT MONEY MARKET,Cash,4.09,1,,,,-4.09, 07/31/2018, DIVIDEND RECEIVED, SPAXX, FIDELITY GOVERNMENT MONEY MARKET,Cash,,,,,,4.09,
After clicking on the file name I get a long MD error message. I am using a Mac. Any ideas as to what is wrong?
Comments are currently closed for this discussion. You can start a new one.
Keyboard shortcuts
Generic
| ? | Show this help |
|---|---|
| ESC | Blurs the current field |
Comment Form
| r | Focus the comment reply box |
|---|---|
| ^ + ↩ | Submit the comment |
You can use Command ⌘ instead of Control ^ on Mac
1 Posted by -Kevin N. on 13 Aug, 2018 11:47 PM
Hi Mike V.
Since you've created a thread regarding Mike Bray's loadsectrans extension, I thought I'd add my comments here too. I hope that you don't mind.
I am also a Fidelity investments client and I'm venturing into Mike Bray's Security Transaction Load extension.
There looks to be some inherent issues with the CSV file generated at Fidelity.
In my file...
1) There are 3 empty rows before the header row. The header row appears in row 4. To resolve this, I deleted the first 3 rows.
2) Each entry has a leading blank space that must be removed. I used Excel's Replace tool to perform this task. For all but the Date column, I set the tool to Replace ',SPACE' with ','. That's a comma then a blank space, replace with just a comma. For the Date column, I set the tool to replace a 'SPACE' with nothing.
3) There is a disclaimer at the bottom of the file which contains commas and it must be deleted.
During my experience with the extension I learned that the extension does not properly load the MM/dd/yyyy date format generated by Fidelity. I had to change the format to dd/MM/yyyy.
I could not get the extension to enter the Security Description field, It appears throughout the extension's windows but is not carried over to the register in Moneydance.
@Mike Bray, When Adding Transaction Types to the extension, which of the 5 'Types' would one select from the drop-down to create Buy and/or Sell transactions? The 5 'Types' offered appear to only pertain to income and expense type of transactions.
-Kevin N. (not a member of MD support)
2 Posted by Mike V. on 14 Aug, 2018 01:08 AM
I too had edited the file down to what I posted. I use TextWrangler. I did not edit the leading spaces though nor change the date format. I have been using GifMaster to convert cvs to gif. I’ll try again with my test file. Thank you for the feedback.
3 Posted by Mike Bray (Quot... on 14 Aug, 2018 01:24 PM
Unfortunately the extension does not do buys and sells, it operates on the cash of an investment account. To do buys and sells you would need number of shares and price to enter a transaction which it is not built to capture.
I wrote this to help me download the dividends and loyalty bonus from Hargreaves Landsdowne hence the format.
It is one of my earliest extensions so the date could well be wrong. I can correct that but not the buys and sells.
4 Posted by -Kevin N. on 14 Aug, 2018 02:16 PM
Hi Mike B.
Thank you for your reply,
Buys and Sells are not a thing that I do a lot of other than settlements in the Money Market Fund for dividends and it's own reinvested dividends - so not a biggie. :)
Fidelity recently announced their intent to implement a more stringent security access. They'll be rolling it out throughout 2018 so I'm looking to any and all other alternatives in case the changes break my current means of access. Namely, OFX via Moneydance and Pocketsense.
Thanks for looking into the Date format. Again, not a biggie. I can reformat it in Excel before importing it. I'll likely implement a macro to make a number of other changes to the Fidelity CSV file as well before importing it.
Any idea why the 'Description' field was not carried into the MD register?
-Kevin N. (not a member of MD support)
5 Posted by Mike Bray (Quot... on 14 Aug, 2018 02:24 PM
Please download again. I have fixed the date format. I will look at description field, it should have been.
Mike
6 Posted by -Kevin N. on 14 Aug, 2018 04:02 PM
Hi Mike B,
MM/dd/yyyy Date format issue resolved in v.2040. Well done. Thank you.
I've attached a few screenshots of the various windows of the extension and the resulting register entries in MD to illustrate the Description issue.
-Kevin N. (not a member of MD support)
7 Posted by Mike Bray (Quot... on 14 Aug, 2018 04:20 PM
On the description field. With a miscInc or Miscexp the description from the transaction is the actual security name and appears in that column. With a Xfr the desciption is just that and appears in that column. The description column tends to be a bit smaller. Please recheck your transactions and try resizing the columns.
I have just run this and the description is appearing in the file.
8 Posted by -Kevin N. on 14 Aug, 2018 04:41 PM
Hi Mike B.
Thank you for the clarification.
I can see now that the Xfr transactions do indeed fill in the Description field.
If all hell breaks loose with Fidelity, I can definitely work with your extension. Thank you for your work on it.
-Kevin N. (not a member of MD support)
9 Posted by Mike V. on 14 Aug, 2018 05:14 PM
Kevin N.,
How do you handle the buys and sells?
10 Posted by -Kevin N. on 14 Aug, 2018 06:00 PM
Hi Mike V.
With regards to using Mike B's extension, they would need to be manually entered. As Mike B replied in post #3 "Unfortunately the extension does not do buys and sells"
As it is right now, I use Moneydance's OFX direct-connect to import all Fidelity transactions.
-Kevin N. (not a member of MD support)
11 Posted by Mike V. on 14 Aug, 2018 10:18 PM
Kevin N.
Thank you for the response. It is not a great solution, but I guess I will keep using Qif Master.
12 Posted by -Kevin N. on 14 Aug, 2018 10:22 PM
Hi Mike V,
Is there a reason for your not using direct-connect?
-Kevin N. (not a member of MD support)
13 Posted by Mike V. on 14 Aug, 2018 11:19 PM
Kevin N.
Not really. Just got use to downloading transactions manually and really did not want to change. Feel like more control maybe ...
14 Posted by -Kevin N. on 14 Aug, 2018 11:28 PM
Ah, OK. Understood. :)
15 Posted by dwg on 14 Aug, 2018 11:29 PM
Downloading transactions means that you are not at the mercy of banks changing their Direct connect systems, breaking them or just stopping the service :)
It's just an observation but some banks also seem to be much too close to Quicken and taking actions that could be taken to favour that company over others or their customers.
16 Posted by -Kevin N. on 14 Aug, 2018 11:37 PM
HI Des,
Downloading from banks is usually pretty straight forward. The offer OFX or QIF files.
Brokerages, a la Fidelity, only offer CSV files; and we all know about importing CSV files into investment accounts in Moneydance. :(
-Kevin N.
17 Posted by dwg on 15 Aug, 2018 06:11 AM
Hi Kevin,
The major banks here seem to only offer QIF files, certainly the files I have downloaded have been in the QIF format (CBA, NAB).
Stock brokers here are not involved in dividend payments. so the downloads from them are generally prices and investment information. Share registries have the Dividend advises but they only have PDF's of the Dividend statements which are of course useless for importing data from.
Investment houses are for your mutual type funds, retirement investments and the like, like in your case the one I currently use only has CSV files, Direct Connect is unknown here. The CSV files are pretty useless as an import source, the "transactions" describe what happens but you need from the description to determine which security is involved and what action type to use, you know I do not mind doing some file manipulation but these files I doubt if I could do it reliably to get a decent QIF file out of it.
Des
18 Posted by -Kevin N. on 15 Aug, 2018 01:40 PM
HI Des,
The notice on the Fidelity website regarding their new, soon-to-come, security measures mentions PFM programs that use screen scraping so I'm hoping that the changes will not have an affect on access to their OFX server.
The head-scratcher for me is if they provide access to an OFX server, why not let you manually download an OFX file?
I'm trying to get in front of this in case Fidelity breaks it all.
-Kevin
19 Posted by Mike V. on 15 Aug, 2018 03:04 PM
Des,
I use Qif Master to convert my Fidelity transactions from cvs to gif.
https://thewoodwards.us/sw/QIFMaster/
20 Posted by dwg on 15 Aug, 2018 09:08 PM
Hi Kevin,
They would probably have to write some more code to be able to create a OFX file for download. Direct Connect works on the basis that the banks OFX server communicates with a client application on your machine. To use this to generate a file I would expect that on the website they would have to emulate a client to talk to that server, but given the website has access to the transactions I envisage it would be easier per se to write a OFX output file like they would write a CSV or QIF file
Screen scraping is rather primitive and usually reserved for those instances where the host side cannot be changed, it means that all effort is on the software vendor to try and get the data and the investment house does zilch. Unless they mean they are going to try and stop it for "security reasons"
Hi Mike,
Both Kevin and myself are windows users.
21 Posted by -Kevin N. on 16 Aug, 2018 01:59 AM
HI Des,
I'm keeping my fingers crossed that Fidelity:
A) Doesn't break Pocketsense or MD's direct-connect.
B) If they do, that they provide an OFX file to manually d/l.
There are apparently third-party PFM's that are screen scraping Fidelity using the clients username and password and Fidelity wants them to sign off on the liability in case of theft of data blah blah blah. Fidelity is beefing up their security measures directed at how those PFM companies access client data.
I just hope it doesn't affect my access to my data - that would be bad.
-Kevin N.
22 Posted by dwg on 16 Aug, 2018 11:13 AM
Hi Kevin,
I never tried to load all the transactions files for my funds, it always just looked like too much trouble and work, I'm retired not a moneydance data entry clerk :). Reckon/Quicken obviously knew it was not always practical to load transactions so had an alternative method that was simple but also effective. You used a fixed price of $1 per unit and shares added was taken to be an increase in value and shared removed a decrease in value and the reports and graphs knew this about funds and handled it accordingly. In Moneydance I have had to devise a way of assigning a "price" to the investment as a whole and revising this price like happens with shares. Technically it is not a real price but it does allow me to track value and changes in value and also to buy and sell units.
The whole investment area could do with major work being done, but I have been saying that for years.
23 Posted by Mike Bray (Quot... on 16 Aug, 2018 01:02 PM
A bit of background.
I wrote the loadsectrans extension to upload transactions from Hargreaves Landsdowne. I have four accounts and each account has two types of transactions (capital and income). Basically I have to download 8 files of transactions, merge them into four and remove blank lines before I can upload them. As you might understand I do not do this very often but I do not have any other way of handling it at the moment. I am loathe to do screen scraping.
24 Posted by -Kevin N. on 16 Aug, 2018 01:59 PM
Hi Des,
I'm quite confused by your investment situation. :) From the sound of it, it seems that you might be better served setting your investments in an asset account or multiple asset accounts - I think?
Hi Mike B.
Screen scraping from the little that I know about it, seems to be a rabbit hole.
I have 4 accounts at Fidelity. I also picture myself, on a monthly basis, jumping through some multiple-file hoops, should Fidelity break their OFX access. I'll look to use your extension or XL2QIF or some combination of the two.
-Kevin N.
25 Posted by dwg on 16 Aug, 2018 09:43 PM
Hi Kevin.
When I migrated I had each account in a separate QIF file (no choice there no export in QIF option in Reckon so had to take a circular approach) and I tried various approaches to setting up my accounts.
Setting up these Investments as an Asset account did not provide the sort of account roll up I wanted nor did it highlight any sort of performance reporting, while Investment accounts did, so I developed an approach to give me a pseudo unit price by using a little custom spreadsheet I developed that on most occasions I enter the $ value of the investment and it gives me a price per unit, I also set it up so I can get the figures I need for buys and sells as well.
I update the "price" monthly. I have three funds and most of the time is spent on the web getting the current value of each fund, I plug the number into the relevant spreadsheet and the three unit prices tat they give me I just put into the security price entry extension.
Overall I probably spend as much time on doing this as I did getting the change in value for Reckon (get current value, deduct previous value and enter the different as a Add or Remove transaction) but for many users the Reckon way is easier, it took some working out to get the spreadsheet right and there are some rounding errors, due to the fact that it is a calculated price based on a calculated number of units so that the number is out but usually something less than 20 cents.
The approach I took was that the initial investment was $1 per unit. I change that price in the future by a calculation based on that cost plus the current value to give me a new unit price. If a buy or sell out of the investment I have to use the current price but I have to backward calculate the number of units based on this price and the $ amount of the investment transaction, so I am always working backwards to get a number of units and a per unit price, given this I expect some minor rounding errors. Given the underlying funds of each investment - and there are about 20, can set their price on a daily or weekly basis and some on different days of the week any calculation of the investment value has a level of approximation in any case.
I'm afraid that the moneydance folks have taken a very narrow focus on investment funds and have not really considered the different scenarios that people can face. The issue of not being able to easily get anything other than the total investment value is a common one in Australia, but people would still want to be able to monitor the overall performance of the investment and classify them correctly, I do not think that the moneydance fallback of using an Asset account is appropriate in these instances, it really needs more flexibility in the Investment account.
26 Posted by -Kevin N. on 16 Aug, 2018 11:01 PM
Hi Des,
For as long as I've known you, you've been offering up improvement ideas for investment accounts in Moneydance. It's unfortunate that they've largely fallen on deaf ears.
Like many of the tips, tricks & good ideas that get floated on these forums, they get lauded by users, support issues them a ticket then they quickly slide into the abyss.
Your investments sound something akin to mutual funds here in 'The States'. A large crop of individual investments housed in a singular fund. The underlying securities are not priced per se but the overall value of the fund is priced out daily after the market settles.
-Kevin
27 Posted by dwg on 17 Aug, 2018 12:19 AM
Hi Kevin,
It's that sort of structure, but it is a further step away. I have a master fund or a wrap account (these two are similar but the beneficial ownership differs) that in turn invests in mutual funds like your Vanguards, Blackrock, UBS funds etc but not into the retail funds these offer but in the wholesale funds that underpins the retail funds.
System closed this discussion on 16 Nov, 2018 12:20 AM.