This forum has been archived. All content is frozen. Please use KDE Discuss instead.

Investments - importing historical prices

Page 1 of 1 (10 posts)

Tags: None
(comma "," separated)
mbeenham
Registered Member
Posts
95
Karma
0
OS

Investments - importing historical prices

Sat Aug 08, 2009 7:58 am
Has anybody got a neat way to import historical prices for investments?

The "Prices" tool is impractical for this purpose and I would like to get prices in for at least this financial year if at all possible. I didn't start with KMyMoney until June and while I have all the historical transactions (buy,sell etc) the prices associated with the securities prior to June don't exist. This makes a nonsense of portfolio performance graphs for the year.

I could get the prices I want to import into a spreadsheet without much difficulty, just need a way to load them...

Thanks

Linux Mint 17.2
KMyMoney Version 4.7.2
Using KDE Development Platform 4.14.2
aga
Registered Member
Posts
85
Karma
0
OS

Re: Investments - importing historical prices

Sat Aug 08, 2009 10:37 am
mbeenham wrote:Has anybody got a neat way to import historical prices for investments?

The "Prices" tool is impractical for this purpose and I would like to get prices in for at least this financial year if at all possible. I didn't start with KMyMoney until June and while I have all the historical transactions (buy,sell etc) the prices associated with the securities prior to June don't exist. This makes a nonsense of portfolio performance graphs for the year.

I could get the prices I want to import into a spreadsheet without much difficulty, just need a way to load them...
Thanks


You don't say from where/what you want to import. Hopefully, you can export a qif file with all your prices? If so, then you might need to tinker with the format of the file.

What is needed is something like this:-

!Option:AutoSwitch
!Account
NBank A
TBank
Daccount no
^
NBank B
TBank
^
[more accounts then investment accounts]
^
NFirst investment accnt
TMutual/stock etc
^
Nnext investment accnt
TMutual
^
Ninvestment accnt
Tstock
^
Nanother inv
Tstock
^
[Then a list of securities with their symbols]
!Clear:AutoSwitch
!Type:Security
NFirst security name
Sticker/symbolA
TUnit/Inv. Trust
^
!Type:Security
Nnext security
SsymbolB
TUnit/Inv. Trust
^
!Type:Security
NsecurityC
SsymbolC
TUnit/Inv. Trust
^
[Then your prices]
!Type:Prices
"symbolA",1.4457,"1/12/04"
^
!Type:Prices
"symbolA",1.4594,"1/1/05"
^
!Type:Prices
"symbolA",1.4589,"1/2/05"
^
!Type:Prices
"symbolA",1.4615,"1/3/05"
^
!Type:Prices
"symbolB",0.7038,"5/12/01"
^
!Type:Prices
"symbolB",0.684,"1/1/02"
^
!Type:Prices
"symbolB",0.692,"1/2/02"
^
!Type:Prices
"symbolB",0.6816,"1/3/02"
^
!Type:Prices
"symbolB",0.6922,"1/4/02"
^
[etc]

Hopefully that should import for you. I seem to remember that Quicken tends to put a garbage entry at the end of each accounts prices.

aga

aga, proud to be a member of KDE forums since 2008-Nov.
mbeenham
Registered Member
Posts
95
Karma
0
OS

Re: Investments - importing historical prices

Sat Aug 08, 2009 11:28 am
Many thanks for that.

I exported from MS Money which doesn't provide price information in the QIF unfortunately.

All the transactions came across OK but of course I lost price updates until I started updating via KMyMoney (which is much more reliable via Yahoo than MS Money was via MSN).

The only way I know to get historical prices is to export them from a MS Money Graph to an Excel spreadsheet and then massage that into the "Prices" format you describe above and create a QIF file.

KMyMoney already knows about all my securities so I think all I probably need is lines in the QIF like:
!Type:Prices
"symbolB",0.6922,"1/4/02"

for each price and date I want to import for each symbol in my investments - correct?

I will try it out later on a COPY of my KMyMoney file with just a couple of prices for a couple of my investments ;)

I will let you know how it goes.
Thanks again

Linux Mint 17.2
KMyMoney Version 4.7.2
Using KDE Development Platform 4.14.2
mbeenham
Registered Member
Posts
95
Karma
0
OS

Re: Investments - importing historical prices

Sun Aug 09, 2009 1:17 am
That worked OK.
I downloaded a test range of historical prices from Yahoo Finance and reformatted:
!Type:Prices
"ATST.L",283.25,"18/5/09"
"ATST.L",284.5,"11/5/09"
"ATST.L",297.75,"5/5/09"
"ATST.L",283,"27/4/09"
"ATST.L",282,"20/4/09"
"ATST.L",271.75,"14/4/09"
"ATST.L",261.5,"6/4/09"
"ATST.L",270,"1/4/09"
^

Imported just fine.
Now for the full download (50 stocks) sigh...
:((

Thanks for the help.

Linux Mint 17.2
KMyMoney Version 4.7.2
Using KDE Development Platform 4.14.2
User avatar
Hei Ku
Registered Member
Posts
784
Karma
3
OS

Re: Investments - importing historical prices

Sun Aug 09, 2009 1:30 am
If you workout a script to do that, then we could include it in KMyMoney for future use.

Hei Ku, proud to be a member of the KMyMoney Development Team since January-2008
mbeenham
Registered Member
Posts
95
Karma
0
OS

Re: Investments - importing historical prices

Sun Aug 09, 2009 10:17 pm
Well, since you asked - it ended up part scripted:

1. I downloaded historical prices from Yahoo finance into files e.g. for ABF.L the download looked like:
Date,Open,High,Low,Close,Volume,Adj Close
2009-06-08,733.50,739.00,724.50,735.50,240000,735.50
2009-06-01,739.50,747.50,721.00,736.00,559600,736.00
2009-05-26,742.50,750.00,729.00,731.00,794000,731.00
2009-05-18,742.00,766.00,741.00,746.50,260200,746.50
2009-05-11,740.50,770.50,731.50,746.00,301700,746.00
2009-05-05,730.50,748.00,718.00,740.50,502000,740.50
2009-04-27,707.00,738.50,692.50,709.00,454800,709.00
2009-04-20,656.00,715.00,647.50,714.00,612000,714.00
2009-04-14,620.00,666.50,616.50,661.50,1572900,661.50
2009-04-06,666.50,673.50,625.50,628.50,1257900,628.50
2009-03-30,635.50,674.50,616.00,658.50,1422300,658.50
2009-03-23,616.50,662.50,614.00,638.00,1305400,638.00
2009-03-16,641.00,656.00,605.50,613.00,1622500,613.00
2009-03-09,622.00,641.00,603.00,632.00,1270700,632.00
2009-03-02,646.50,649.50,608.50,621.50,1439400,621.50
2009-02-23,649.00,672.50,637.00,651.00,1770000,651.00
2009-02-16,663.50,680.00,638.50,645.00,1064400,645.00
2009-02-09,664.00,684.50,651.50,664.50,939800,664.50
2009-02-02,657.50,683.00,646.00,664.00,1090700,664.00
2009-01-26,665.50,681.50,652.50,663.00,1414200,663.00
2009-01-19,685.50,689.50,653.00,666.00,1625700,666.00
2009-01-12,722.00,732.00,660.00,675.50,1516200,675.50
2009-01-05,743.50,752.00,712.00,717.50,1006900,717.50
2008-12-29,714.00,757.00,708.50,740.00,694000,740.00

I used the weekly option and named this file with its symbol ABF.L, similarly for the other securities.


2. I then ran the following script I called qiffles.sh:
#!/bin/bash
#EDIT - n.b. following line for securities on London stockex.
#EDIT - change appropriate to use.
for file in *.L
do
awk -F , \
'BEGIN {print "!Type:Prices"}\
{ if ( FNR > 1 ) printf ("\"%s\",%6.4f,\"%s\"\n",FILENAME,$5/100,$1)}\
END {print "^"}' $file
done

./qiffiles.sh > import.qif

which massaged and concatenated all my import files into a single QIF:
!Type:Prices
"ABF.L",7.3550,"2009-06-08"
"ABF.L",7.3600,"2009-06-01"
"ABF.L",7.3100,"2009-05-26"
"ABF.L",7.4650,"2009-05-18"
"ABF.L",7.4600,"2009-05-11"
"ABF.L",7.4050,"2009-05-05"
"ABF.L",7.0900,"2009-04-27"
"ABF.L",7.1400,"2009-04-20"
"ABF.L",6.6150,"2009-04-14"
"ABF.L",6.2850,"2009-04-06"
"ABF.L",6.5850,"2009-03-30"
"ABF.L",6.3800,"2009-03-23"
"ABF.L",6.1300,"2009-03-16"
"ABF.L",6.3200,"2009-03-09"
"ABF.L",6.2150,"2009-03-02"
"ABF.L",6.5100,"2009-02-23"
"ABF.L",6.4500,"2009-02-16"
"ABF.L",6.6450,"2009-02-09"
"ABF.L",6.6400,"2009-02-02"
"ABF.L",6.6300,"2009-01-26"
"ABF.L",6.6600,"2009-01-19"
"ABF.L",6.7550,"2009-01-12"
"ABF.L",7.1750,"2009-01-05"
"ABF.L",7.4000,"2008-12-29"
^
!Type:Prices
"APT.L",0.3775,"2009-06-03"
"APT.L",0.3825,"2009-05-27"
"APT.L",0.3850,"2009-05-18"
"APT.L",0.4100,"2009-05-11"
"APT.L",0.4350,"2009-05-05"
etc......

N.B. The symbol is taken from the downloaded file name so it is important to name it correctly.

3. I then imported into KMyMoney.
Job done, start to finish <30 seconds.

(+writing the script about 45 minutes, +downloading the files about 10 minutes)

It would have been nice to automate the download, but not worth it for a one-off.

Hope this helps someone.

Linux Mint 17.2
KMyMoney Version 4.7.2
Using KDE Development Platform 4.14.2
User avatar
Hei Ku
Registered Member
Posts
784
Karma
3
OS

Re: Investments - importing historical prices

Mon Aug 10, 2009 2:04 am
Thanks a lot. I know this will be helpful to other people.

Hei Ku, proud to be a member of the KMyMoney Development Team since January-2008
User avatar
ipwizard
KDE Developer
Posts
1359
Karma
6
OS

Re: Investments - importing historical prices  Topic is solved

Mon Aug 10, 2009 9:45 am
In case someone wants to automate this further, here's a possible way to extract all symbols and sources from a KMyMoney file:

Code: Select all
#!/usr/bin/perl
#
# extract-online-symbol
#
# takes the KMyMoney XML structure and outputs a list of quoted
# pairs showing the symbol name and the price source
#
# Symbols for which no price source is available are not listed

my $symbol;
my $scanSource = 0;

while(<>) {
  $scanSource = 0 if($_ =~ /<\/SECURITY>/);
  if($_ =~ /<SECURITY .* symbol="([^"]+)".*[^\/]>/) {
    $symbol = $1;
    $scanSource = 1;
  }
  if($scanSource == 1) {
    if($_ =~ /key="kmm-online-source" value="([^"]+)"/) {
      print "\"$symbol\" \"$1\"\n"
    }
  }
}


Usage would be:

Code: Select all
./extract-online-symbol < thb.xml


or in case you use the compressed form:

Code: Select all
zcat thb.kmy | ./extract-online-symbol


or for the GPG to encrypted data:

Code: Select all
gpg -d thb.kmy | ./extract-online-symbol

ipwizard, proud to be a member of the KMyMoney forum since its beginning. :-D
openSuSE Leap 15.4 64bit, KF5
empenoso
Registered Member
Posts
31
Karma
0

Re: Investments - importing historical prices

Sat Jan 14, 2017 3:15 pm
Can I set currency for quotes? I import the price file. But the currency stated in my default currency KMyMoney.
But I need to specify the currency (US dollars) for these historical prices:

Code: Select all
!Type:Prices
"^GSPC",1517.9300000,"04/02/13"
"^GSPC",1519.7900000,"11/02/13"
"^GSPC",1515.6000000,"18/02/13"
"^GSPC",1518.2000000,"25/02/13"
"^GSPC",1551.1800000,"04/03/13"
...
"^GSPC",2263.7900000,"19/12/16"
"^GSPC",2238.8300000,"26/12/16"
"^GSPC",2276.9800000,"02/01/17"
"^GSPC",2274.6400000,"09/01/17"
^


Thanks for the advice!
mdolnik
Registered Member
Posts
14
Karma
0

Re: Investments - importing historical prices

Fri Apr 03, 2020 5:58 pm
I found another way to successfully import historical prices https://forum.kde.org/viewtopic.php?f=69&t=165308&p=430514#p430514

Page 1 of 1 (10 posts)

Bookmarks


Who is online

Registered users: bartoloni, Bing [Bot], Evergrowing, Google [Bot], ourcraft