Benchmarking

[Urbandreamer]

I decided that it would be interesting to benchmark my portfolio against other things.

In this case TRIUKX (FTSE total return).

A google search found a website that I could view the last 5 years of how it performed.
Unfortunately You could not download it as a csv.
So, cut and paste to a text editor (not word processor).

Ok so now to the processing.
I use *nix command line tools, you may think of them as linux tools, though they often predate linux.

The date format was odd. I used a combination of awk and sed to produce a date in the same format as the csv file containing my back history.
While I was at it I extracted the dates from my records using awk.

I then used grep and a "diff" package to identify dates that the two sets didn't share. It seems that the website giving me TRIUKX is missing a month!
Ok I ensured that I had two data sets that contained the same dates in the same order. I had to use tac, as the website worked most recent first while HYPTUSS puts the most recent at the end.

After that is was easy.

Tools used.
awk, available to run on windows if you search. Possibly known as gawk.
sed, dito.
grep, also available but often installed with any compiler.
diff, I used Kdiff3, also available for windows but you might like WinMerge instead.
tac, simply reverses the order of a file. It's cat spelt backwards. cat is the routine to list a file in *nix. You may use sort in the spreadsheet package instead.

I hope this is of some use to those who wish to produce csv files to benchmark their portfolio. A search will provide info about how easy awk and sed are to use, though human nature means that nobody remembers and we always have to look it up again.

Ps, I didn't use it but the "paste" command would enable a single csv to be produced containing both data sets, rather than load each into its own sheet in a spreadsheet workbook.

[ReformedCharacter]

That's admirable but a lot of work, if you go here:

https://uk.investing.com/indices/ftse-1 ... rical-data

Select Daily or Weekly Time Frame then select the Date selector - you can go back until 2012.

Select and copy the data only.

Open Excel and on a worksheet select Paste Special. Choose Text and then OK.

That will give you the Date, Price etc in separate rows and columns.

Probably the Libre Office equivalent would do something similar if you're not a Win user.

RC

[Breelander]

That's admirable but a lot of work, if you go here:

https://uk.investing.com/indices/ftse-1 ... rical-data

Select Daily or Weekly Time Frame then select the Date selector - you can go back until 2012....

28 Dec 2012 to be precise....

Select and copy the data only.
Open Excel and on a worksheet select Paste Special. Choose Text and then OK.
That will give you the Date, Price etc in separate rows and columns.
Probably the Libre Office equivalent would do something similar if you're not a Win user.

Actually, while you were writing that reply I did just that - and yes (even though a Win user here) to keep in the *nix spirit I used LibreOffice Calc, works just as well. A spreadsheet also makes it easy to remove the unwanted columns (open, high, low, vol, % change) leaving just date and price.

The date format was odd....

It was here too. the dates look OK at first glance, but they have not actually been pasted as dates, they are really text strings, eg. "Aug 14, 2020"

To convert to a date format I could use in a spreadsheet I saved as a .csv then open in a text editor (Notepad). First use 'replace all' to remove all quotes " characters, then replace each three character month plus space with a number and comma (eg. replace all 'Aug ' with '8,') and save.

Now open in LibreOffice and you have month, day and year columns. Reorder them as day, month and year and save as csv again.

Then edit in Notepad, "Aug 14, 2020" will by now be 14,8,2020. For each month number, use 'replace all ' to replace ,n, with /n/ eg. replace ,8, with /8/ then save.

You now have a .csv with two columns, the first a true date format (eg 14/8/2020), the second the price. You can open this in Excel or LibreOffice Calc and it will have proper dates.

[ReformedCharacter]

It was here too. the dates look OK at first glance, but they have not actually been pasted as dates, they are really text strings, eg. "Aug 14, 2020"

To convert to a date format I could use in a spreadsheet I saved as a .csv then open in a text editor (Notepad). First use 'replace all' to remove all quotes " characters, then replace each three character month plus space with a number and comma (eg. replace all 'Aug ' with '8,') and save.

Now open in LibreOffice and you have month, day and year columns. Reorder them as day, month and year and save as csv again.

Then edit in Notepad, "Aug 14, 2020" will by now be 14,8,2020. For each month number, use 'replace all ' to replace ,n, with /n/ eg. replace ,8, with /8/ then save.

You now have a .csv with two columns, the first a true date format (eg 14/8/2020), the second the price. You can open this in Excel or LibreOffice Calc and it will have proper dates.

I also use TRIUKX for benchmarking purposes and some time in the past followed a similar exercise to extract the data to spreadsheet. I think I just entered a start date in another column and then in the row below added 7 days to the date - to get the date a week later. I then copied the formula down until the end of the range and checked that the newly created dates tallied with the originals.

RC

[Urbandreamer]

The advantage of sed and awk is that they can be used in a script file, so repeating the process is just a case of running it.

Code: Select all

sed 's/,//g' TRIUKX.txt | awk '{if ($1=="Date") printf"%s,%s\n",$1,$2; else printf "%s-%s-%s,%s\n", $1, $2, $3, $4}'

produces

Code: Select all

Date,Price
Aug-14-2020,5769.14
Aug-13-2020,5859.66
Aug-12-2020,5928.31
...


Which Librecalc can read.
If I wanted to replace month text with a number then I would certainly write a script.