Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to johnstevens77,Bhoddhisatva,scotia,Anonymous,Cornytiv34, for Donating to support the site

HYPTUSS and LibreOffice

Discussions regarding financial software
granretire
2 Lemon pips
Posts: 236
Joined: November 4th, 2016, 7:03 pm
Has thanked: 616 times
Been thanked: 48 times

HYPTUSS and LibreOffice

#463154

Postby granretire » December 4th, 2021, 8:28 pm

Having purchased a new pc and too mean to pay MS for Office, I have installed LibreOffice and started to set up my data in it. I duly copied the tickers and found that they now correspond to a completely different set of companies! What am I doing that is stupid?
I got prices - looked OK for my tickers, and then sharecast yields. A few were unable to come up with a latest or forecast yield that normally manage it in my old Excel spreadsheet.
This is my first dable with libreoffice, so no doubt I am doing something silly.
Windows 11, libre 7.1

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: HYPTUSS and LibreOffice

#463163

Postby kiloran » December 4th, 2021, 9:19 pm

Sorry to hear about your problems. It must give you a poor impression of LibreOffice, which is sad. I've used LibreOffice for years for everything I do, and it works well, I only use Excel to support HYPTUSS.

Unfortunately, I have no idea what your problem is, I just don't have enough information. Exactly how did you copy the tickers? And what do you mean when you say they correspond to a completely different set of companies? Please be very precise and give some details. Some screenshots would be nice.

--kiloran

granretire
2 Lemon pips
Posts: 236
Joined: November 4th, 2016, 7:03 pm
Has thanked: 616 times
Been thanked: 48 times

Re: HYPTUSS and LibreOffice

#463197

Postby granretire » December 5th, 2021, 8:21 am

Sorry I didn't give enough detail.
I opened my Excel data version of HYPTUSS using LO (call it A), accepting that I did not expect it would work. And opened the LO version (call it B). I enabled macros.
I then copied the column of tickers from A to B. Immediately it showed the the incorrect companies for the tickers
A
[url][[url=https://postimg.cc/Btzcd4S7]Image[/url]]
B
[url][[url=https://postimg.cc/Wd1NgLrj]Image[/url]]

Hope the screen shots work - another new one for me - adding screenshots to lemonfool. They say you are never too old to learn!

mc2fool
Lemon Half
Posts: 7812
Joined: November 4th, 2016, 11:24 am
Has thanked: 7 times
Been thanked: 3017 times

Re: HYPTUSS and LibreOffice

#463206

Postby mc2fool » December 5th, 2021, 9:56 am

granretire wrote:Hope the screen shots work - another new one for me - adding screenshots to lemonfool. They say you are never too old to learn!

Hint: if you use the Preview button then you don't have to hope, you can see what's worked, and fix what hasn't. ;)

P.S. You can also edit your post for a few minutes after submitting it, by clicking on the pencil button that appears at the top right of the post during that period.

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: HYPTUSS and LibreOffice

#463219

Postby seagles » December 5th, 2021, 10:34 am

granretire wrote:Sorry I didn't give enough detail.
I opened my Excel data version of HYPTUSS using LO (call it A), accepting that I did not expect it would work. And opened the LO version (call it B). I enabled macros.
I then copied the column of tickers from A to B. Immediately it showed the the incorrect companies for the tickers
A
[Image]
B
[Image]

Hope the screen shots work - another new one for me - adding screenshots to lemonfool. They say you are never too old to learn!


Tidied up for ease of viewing. Click on image for larger view.
Last edited by seagles on December 5th, 2021, 10:35 am, edited 1 time in total.

granretire
2 Lemon pips
Posts: 236
Joined: November 4th, 2016, 7:03 pm
Has thanked: 616 times
Been thanked: 48 times

Re: HYPTUSS and LibreOffice

#463220

Postby granretire » December 5th, 2021, 10:35 am

Thanks seagles

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: HYPTUSS and LibreOffice

#463250

Postby kiloran » December 5th, 2021, 12:46 pm

Well, I'm flummoxed! I can't find any way to reproduce your problem (using Excel 2010 and 2016, and LibreOffice 7.1.4.2). I assume you are following the migration process on page 26 of the User Guide.

On your LibreOffice version, you have RIO in cell C6. This shows B6 = Royal Dutch Shell B and D6 = Oil and Gas producers, which is clearly wrong
What are the formulas in B6 and D6? I have:
B6: =INDEX('Company Data Sheet'.$B:$B, MATCH(IF(ISNUMBER(C6),TEXT($C6,0),C6),'Company Data Sheet'.$C:$C,0), 1)
D6: =INDEX('Company Data Sheet'.$D:$D, MATCH(IF(ISNUMBER(C6),TEXT($C6,0),C6),'Company Data Sheet'.$C:$C,0), 1)

On a brand new unedited HYPTUSS version b-p, do you have the same formulas?

--kiloran

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: HYPTUSS and LibreOffice

#463251

Postby seagles » December 5th, 2021, 12:54 pm

kiloran wrote:Well, I'm flummoxed! I can't find any way to reproduce your problem (using Excel 2010 and 2016, and LibreOffice 7.1.4.2). I assume you are following the migration process on page 26 of the User Guide.

On your LibreOffice version, you have RIO in cell C6. This shows B6 = Royal Dutch Shell B and D6 = Oil and Gas producers, which is clearly wrong
What are the formulas in B6 and D6? I have:
B6: =INDEX('Company Data Sheet'.$B:$B, MATCH(IF(ISNUMBER(C6),TEXT($C6,0),C6),'Company Data Sheet'.$C:$C,0), 1)
D6: =INDEX('Company Data Sheet'.$D:$D, MATCH(IF(ISNUMBER(C6),TEXT($C6,0),C6),'Company Data Sheet'.$C:$C,0), 1)

On a brand new unedited HYPTUSS version b-p, do you have the same formulas?

--kiloran


Could it be a "CORRUPT" data sheet? I remember a while back I copied a "new" version of the data sheet and forgot I had modifed some entries, got a similar result as the lines did not match, whoops....

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: HYPTUSS and LibreOffice

#463263

Postby kiloran » December 5th, 2021, 1:27 pm

seagles wrote:
kiloran wrote:Well, I'm flummoxed! I can't find any way to reproduce your problem (using Excel 2010 and 2016, and LibreOffice 7.1.4.2). I assume you are following the migration process on page 26 of the User Guide.

On your LibreOffice version, you have RIO in cell C6. This shows B6 = Royal Dutch Shell B and D6 = Oil and Gas producers, which is clearly wrong
What are the formulas in B6 and D6? I have:
B6: =INDEX('Company Data Sheet'.$B:$B, MATCH(IF(ISNUMBER(C6),TEXT($C6,0),C6),'Company Data Sheet'.$C:$C,0), 1)
D6: =INDEX('Company Data Sheet'.$D:$D, MATCH(IF(ISNUMBER(C6),TEXT($C6,0),C6),'Company Data Sheet'.$C:$C,0), 1)

On a brand new unedited HYPTUSS version b-p, do you have the same formulas?

--kiloran


Could it be a "CORRUPT" data sheet? I remember a while back I copied a "new" version of the data sheet and forgot I had modifed some entries, got a similar result as the lines did not match, whoops....

Yes, it certainly could be, seagles, but I assumed that granretire had just copied the tickers across to the fresh copy of HYPTUSS when the problem became apparent. If more changes have been made to HYPTUSS before the problem showed up, then we need to know more,

--kiloran

granretire
2 Lemon pips
Posts: 236
Joined: November 4th, 2016, 7:03 pm
Has thanked: 616 times
Been thanked: 48 times

Re: HYPTUSS and LibreOffice

#463269

Postby granretire » December 5th, 2021, 1:37 pm

Yes, Kiloran, I did the standard copy the tickers. I have not touched the data sheet in LO. Will repeat the exercise wth a new downloaded copy and let you know if it changes (or not) anything

seagles
Lemon Slice
Posts: 490
Joined: August 19th, 2017, 8:37 am
Has thanked: 153 times
Been thanked: 235 times

Re: HYPTUSS and LibreOffice

#463270

Postby seagles » December 5th, 2021, 1:41 pm

granretire wrote:Yes, Kiloran, I did the standard copy the tickers. I have not touched the data sheet in LO. Will repeat the exercise wth a new downloaded copy and let you know if it changes (or not) anything


Before copying the TICKERS. Try doing the "add share" feature on the "vanilla" spreadsheet and add RIO. Is it correct?

granretire
2 Lemon pips
Posts: 236
Joined: November 4th, 2016, 7:03 pm
Has thanked: 616 times
Been thanked: 48 times

Re: HYPTUSS and LibreOffice

#463273

Postby granretire » December 5th, 2021, 1:52 pm

'Add share' appeared to work OK, ie got the correct company name displayed.
Had another go with clean LO version, copied the tickers from the Excel version (opened in LO) and got the duff companies again.

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: HYPTUSS and LibreOffice

#463286

Postby kiloran » December 5th, 2021, 2:32 pm

granretire wrote:'Add share' appeared to work OK, ie got the correct company name displayed.
Had another go with clean LO version, copied the tickers from the Excel version (opened in LO) and got the duff companies again.

Ah! I can now reproduce the problem!!!!
I had not considered that you were opening the old Excel version using LibreOffice, though with a new PC it's understandable.

So, I opened a fresh copy of version b-p, then opened an old xls version of HYPTUSS in LibreOffice. I selected the list of tickers in the xls version, then pasted them into version b-p, and got screwed up company names and sectors. So far, I have no idea why. The tickers look OK, and the formulas look OK. Weird. Very, very weird. :?

The solution is simple...... instead of a simple paste, use paste special and just paste the values. That seems to be fine.

It's really intriguing, I'll have a look at it later and try to understand what it happening.

--kiloran

granretire
2 Lemon pips
Posts: 236
Joined: November 4th, 2016, 7:03 pm
Has thanked: 616 times
Been thanked: 48 times

Re: HYPTUSS and LibreOffice

#463290

Postby granretire » December 5th, 2021, 3:07 pm

Well, we all like a challenge ;)
Yes, I have got it to paste correctly using 'paste unformatted text'. I notice that as a result of doing this, the curent price and number of shares for each company is increased by one going down the respective columns. And the company data sheet has not been updated with GRIO, SOHO, OSB, CURY, CLIN, TRCS, MTVW. I know that you updated the company sheet for these companies in the Excel version. Please don't ake this as a complaint. I am very grateful for the work that you have done for me on my spreadsheet problem

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: HYPTUSS and LibreOffice

#463771

Postby kiloran » December 7th, 2021, 10:41 am

kiloran wrote:
granretire wrote:'Add share' appeared to work OK, ie got the correct company name displayed.
Had another go with clean LO version, copied the tickers from the Excel version (opened in LO) and got the duff companies again.

Ah! I can now reproduce the problem!!!!
I had not considered that you were opening the old Excel version using LibreOffice, though with a new PC it's understandable.

So, I opened a fresh copy of version b-p, then opened an old xls version of HYPTUSS in LibreOffice. I selected the list of tickers in the xls version, then pasted them into version b-p, and got screwed up company names and sectors. So far, I have no idea why. The tickers look OK, and the formulas look OK. Weird. Very, very weird. :?

The solution is simple...... instead of a simple paste, use paste special and just paste the values. That seems to be fine.

It's really intriguing, I'll have a look at it later and try to understand what it happening.

--kiloran

This is just weird, I haven't got a scoobie what's going on.

If I open a fresh copy of the LibreOffice version b-p using LibreOffice, everything works OK. I can add shares using the button, or I can drag the last row of data down and overtype the ticker, and everything updates OK. I get the correct company name and sector.

However, if I open a fresh copy of the LibreOffice version b-p using LibreOffice, and then open the Excel version of HYPTUSS using LibreOffice, things go screwy. While both documents are open, I go to version b-p, drag down the last data row, then manually overtype a ticker, I get the wrong company name and sector, even though the formulas for the name and sector are correct. If I add a new share using the Add Share button, that works OK. If I select a cell with an incorrect company name, copy it and then paste it into the same cell, it then displays the correct name. If I go to the libreoffice menu DATA/CALCULATE/RECALCULATE HARD, all the formulas then recalculate and show the correct data.

It's clear that the Excel version being open in LibreOffice is causing the problem, but I can't explain it. If it prevented the formulas recalculating, I would expect those cells to display 0 until recalculation is triggered. But why it displays company name Royal Dutch Shell B for a ticker of RIO, I have no idea, the two companies are about 10rows apart in the Company Data Sheet.

I won't spend any more time on it, it's clear it's a weird quirk which is easily overcome by a hard recalculation. Opening the Excel version in LibreOffice certainly will not work correctly due to the VBA macros being incompatible with LibreOffice, but it does open and display the data in the Excel version though I cannot see how it would affect the calculation of the formulas in the LibreOffice version.

Very, very strange

--kiloran

granretire
2 Lemon pips
Posts: 236
Joined: November 4th, 2016, 7:03 pm
Has thanked: 616 times
Been thanked: 48 times

Re: HYPTUSS and LibreOffice

#463779

Postby granretire » December 7th, 2021, 11:02 am

Many thanks for letting me know your research on this weird feature. Sorry I caused so much trouble.
Any chance of the company data sheet being updated with the companies that were there for excel, but not for LO, please? With my present rate of messing things up, I would be happier if you did it

kiloran
Lemon Quarter
Posts: 4092
Joined: November 4th, 2016, 9:24 am
Has thanked: 3234 times
Been thanked: 2827 times

Re: HYPTUSS and LibreOffice

#463806

Postby kiloran » December 7th, 2021, 11:57 am

granretire wrote:Many thanks for letting me know your research on this weird feature. Sorry I caused so much trouble.
Any chance of the company data sheet being updated with the companies that were there for excel, but not for LO, please? With my present rate of messing things up, I would be happier if you did it

The Excel and LibreOffice versions of HYPTUSS contain identical copies of the company data sheet. Between updates pf HYPTUSS, updated versions of the company data sheet are uploaded to http://lemonfoolfinancialsoftware.weebl ... op-up.html (the last update was yesterday).

To update your HYPTUSS, see page 18 of the User Guide
To update the HYPTUSS Company Data Sheet:
1. download the tab-delimited text file company_data_sheet.txt from
http://tinyurl.com/y9atwtb7 and save it on your PC in any convenient location (typically,
the Downloads folder)
2. Open your HYPTUSS and open the Company Data Sheet
3. Select the row of the HYPTUSS Company Data Sheet where you want to import data.
Normally, this is Row 1, but if you have some customised rows at the top of the data sheet,
select a row after your customised rows (in the example shown above, select row 8)
4. Click on the button in Cell A1. This will open a dialog to allow you to select the downloaded
file company_data_sheet.txt. Select this file, then click on OK. The new data will be
imported, starting at your selected row, overwriting all subsequent rows


--kiloran


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 5 guests