Simple Yahoo Price Scrape update

[kiloran]

I've updated the LibreOffice/OpenOffice version to 1-3, available at: http://lemonfoolfinancialsoftware.weebl ... crape.html
This uses the same code as the current HYPTUSS and should overcome the problems seen by some users.

The Excel version is not currently working. It will be fixed, but in the meantime I would point users to eventide's excellent Excel add-in:
viewtopic.php?f=27&t=8332

--kiloran

[mike]

I've updated the LibreOffice/OpenOffice version to 1-3, available at: http://lemonfoolfinancialsoftware.weebl ... crape.html
This uses the same code as the current HYPTUSS and should overcome the problems seen by some users.

--kiloran

Hi Kiloran

Sorry to let you know, but this doesn't work for me.

Open Office 4.1.3, Windows 7 professional

Error message is "No data returned from Yahoo"
The B & C column pre-populated are then blank

A huge thanks for all your work in overcoming the Yahoo change
Mike

[kiloran]

Hi Kiloran

Sorry to let you know, but this doesn't work for me.

Open Office 4.1.3, Windows 7 professional

Error message is "No data returned from Yahoo"
The B & C column pre-populated are then blank

A huge thanks for all your work in overcoming the Yahoo change
Mike

Now that's interesting, Mike. It essentially uses the same code as HYPTUSS b-f-dev4 which does(did??) work for you a few days ago.

But..... good news! I've just tried it with OO Portable 4.1.3 on Windows 7 and see the same problem
Something for me to get my teeth into, at last. Nothing worse than trying to fix a bug I can't see

--kiloran

[mike]

.... uses the same code as HYPTUSS b-f-dev4 which does(did??) work for you a few days ago.

--kiloran

Does !

Just checked both again, and the same result - the HYPTUSS dev4 is fine, but the Yahoo Price Scrape returns the error as above.

Mike

OO 4.1.3, W7 Prof

[kiloran]

I've updated the LibreOffice/OpenOffice version to 1-3, available at: http://lemonfoolfinancialsoftware.weebl ... crape.html
This uses the same code as the current HYPTUSS and should overcome the problems seen by some users.

--kiloran

Sorry to let you know, but this doesn't work for me.
Open Office 4.1.3, Windows 7 professional
Error message is "No data returned from Yahoo"

Mike, I've fixed the problem, and published version 1-3a at: http://lemonfoolfinancialsoftware.weebl ... crape.html
A simple fix, my aged brain missed out a simple line of code, so it was OK in LibreOffice but not OpenOffice.

And Itsallaguess has worked his magic to overcome the Yahoo change and the Excel version 1-1 is also published at the above link

--kiloran

[mike]

I've fixed the problem, and published version 1-3a at: http://lemonfoolfinancialsoftware.weebl ... crape.html
A simple fix, my aged brain missed out a simple line of code, so it was OK in LibreOffice but not OpenOffice.

--kiloran

Thanks Kiloran, all fine now (OO 4.1.4, W8.1)

Itsallaguess has worked his magic to overcome the Yahoo change and the Excel version 1-1 is also published at the above link

Sorry, don't have Excel so can't check out iaag's version.

Really appreciate your work, thank you
Mike

[JMN2]

How can I see the python code in this in the LibreOffice version and perhaps modify and save it?

Currently I am running the yahoo prices in this and copy pasting into my spreadsheets. I tried copying my worksheets into this new scrape but all fonts and formatting looked strange. Obviously I can't just copy this worksheet and the code into my existing spreadsheet - I don't even see the python code anywhere in the macros.

The old macro code was VBA, easily found and modified. This new stuff seems so secretive.

Also, how permanent does this new Yahoo stuff seem, any idea if Yahoo will change their system again and all these new fixes from Kiloran et al will become obsolete?

[kiloran]

How can I see the python code in this in the LibreOffice version and perhaps modify and save it?

Currently I am running the yahoo prices in this and copy pasting into my spreadsheets. I tried copying my worksheets into this new scrape but all fonts and formatting looked strange. Obviously I can't just copy this worksheet and the code into my existing spreadsheet - I don't even see the python code anywhere in the macros.

The old macro code was VBA, easily found and modified. This new stuff seems so secretive.

It's so obvious..... Open the spreadsheet using an archive manager such as 7-zip or winzip, open the Scripts folder and then the python folder, and you will see a file SimpleYahooPriceScrape.py, which contains the python script.
There, I said it was obvious

Also, how permanent does this new Yahoo stuff seem, any idea if Yahoo will change their system again and all these new fixes from Kiloran et al will become obsolete?

It seems permanent according to my very cloudy crystal ball. Until tomorrow. Or the day after. Quite simply.... nobody knows.

Shout if you need more help on the python bits and pieces

--kiloran

[JMN2]

...

Shout if you need more help on the python bits and pieces

--kiloran

Thanks! If and when I get the python code showing as you explained, can I somehow take it and attach it to my existing libreoffice spreadsheet file where there currently isn't any python?

Ie would it be obvious where to find the corresponding place and copy over?

Yes, I don't have a clue...

[JMN2]

I was able to extract, saw the python file....

then etracted similarly the target spreadsheet which obviously was missing the scripts and python files.

Copied over the folder, copied also the relevant worksheet into the spreadsheet, ran it...didn't work...

obviously this is not how it works...like in vba one would copy the module over and the macro would work in a new place...

Need to "compile" I guess...

[JMN2]

I think I am close. In tools, macros, organise macros, python, under the price scrape I can see the .py python code, thus the macro button is also assigned to it

but my spreadsheet where I copied into the python code into the folder it is missing. How do I bring the code into my spreadsheet? Cheers.

[kiloran]

I was able to extract, saw the python file....

then etracted similarly the target spreadsheet which obviously was missing the scripts and python files.

Copied over the folder, copied also the relevant worksheet into the spreadsheet, ran it...didn't work...

obviously this is not how it works...like in vba one would copy the module over and the macro would work in a new place...

Need to "compile" I guess...

No, there's no compiling required with python.

Here's a few things you need to do....

1. The python script is designed to work with data in a sheet named Sheet1. Case sensitive
2. The python script must be in a folder Scripts/python. Case sensitive.
3. You need to edit the file manifest.xml in the META-INF folder.
   It probably looks something like this:
   <?xml version="1.0" encoding="UTF-8"?>
   <manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0" manifest:version="1.2">
   <manifest:file-entry manifest:full-path="/" manifest:version="1.2" manifest:media-type="application/vnd.oasis.opendocument.spreadsheet"/>
   <manifest:file-entry manifest:full-path="Configurations2/" manifest:media-type="application/vnd.sun.xml.ui.configuration"/>
   <manifest:file-entry manifest:full-path="styles.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="manifest.rdf" manifest:media-type="application/rdf+xml"/>
   <manifest:file-entry manifest:full-path="meta.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="Thumbnails/thumbnail.png" manifest:media-type="image/png"/>
   <manifest:file-entry manifest:full-path="content.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="settings.xml" manifest:media-type="text/xml"/>
   </manifest:manifest>
   
   You need to add the 3 lines in red so that it looks something like this:
   <?xml version="1.0" encoding="UTF-8"?>
   <manifest:manifest xmlns:manifest="urn:oasis:names:tc:opendocument:xmlns:manifest:1.0" manifest:version="1.2">
   <manifest:file-entry manifest:full-path="/" manifest:version="1.2" manifest:media-type="application/vnd.oasis.opendocument.spreadsheet"/>
   <manifest:file-entry manifest:full-path="Scripts/python/SimpleYahooPriceScrape.py" manifest:media-type=""/>
   <manifest:file-entry manifest:full-path="Scripts/python/" manifest:media-type="application/binary"/>
   <manifest:file-entry manifest:full-path="Scripts/" manifest:media-type="application/binary"/>
   <manifest:file-entry manifest:full-path="Basic/Standard/Module1.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="Basic/Standard/Module2.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="Basic/Standard/Module3.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="Basic/Standard/Module4.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="Basic/Standard/script-lb.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="Basic/script-lc.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="Configurations2/accelerator/current.xml" manifest:media-type=""/>
   <manifest:file-entry manifest:full-path="Configurations2/" manifest:media-type="application/vnd.sun.xml.ui.configuration"/>
   <manifest:file-entry manifest:full-path="styles.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="manifest.rdf" manifest:media-type="application/rdf+xml"/>
   <manifest:file-entry manifest:full-path="meta.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="Thumbnails/thumbnail.png" manifest:media-type="image/png"/>
   <manifest:file-entry manifest:full-path="content.xml" manifest:media-type="text/xml"/>
   <manifest:file-entry manifest:full-path="settings.xml" manifest:media-type="text/xml"/>
   </manifest:manifest>

There are other differences in the 2 example xml files above, but don't worry about that. The key thing is to add in the lines in red

Hope this helps.

--kiloran

[JMN2]

Thanks Kiloran, I did notice the few lines in manifest file and copied over, no joy.

While copying the 3 rows over I managed to blank out the contents in the destinatio meta file so that's probably the problem...

no it's not blank, when I open edit in notepad the text is there but when I just open it it is blank. But the other one is not blank when I open it.

[JMN2]

Not happening. I have the script in the metafile python folder unchanged and I added the 3 lines and can see them inside the file, my spreadsheet just isn't showing the .py script like the scrape spreadsheet is.

Even if I just copy the metafile over as a whole it's not working so it does not seem like I'm messing up the three rows.

[kiloran]

Not happening. I have the script in the metafile python folder unchanged and I added the 3 lines and can see them inside the file, my spreadsheet just isn't showing the .py script like the scrape spreadsheet is.

You can email it to me at the email address at the bottom of http://lemonfoolfinancialsoftware.weebly.com/ if you want me to take a look at it.

--kiloran

[JMN2]

Not happening. I have the script in the metafile python folder unchanged and I added the 3 lines and can see them inside the file, my spreadsheet just isn't showing the .py script like the scrape spreadsheet is.

You can email it to me at the email address at the bottom of http://lemonfoolfinancialsoftware.weebly.com/ if you want me to take a look at it.

--kiloran

But the problem is I haven't changed anything, Scripts /python folders are copied, manifest file is copied or 3 rows added, so why is the scrape seeing the macro but my own isn't?

I extracted the spreadsheet files using 7 zip and now have all these folders which look identical between my spreadsheet and the scrape one. The worksheet name is identical.

Thanks for the offer.

[kiloran]

Not happening. I have the script in the metafile python folder unchanged and I added the 3 lines and can see them inside the file, my spreadsheet just isn't showing the .py script like the scrape spreadsheet is.

You can email it to me at the email address at the bottom of http://lemonfoolfinancialsoftware.weebly.com/ if you want me to take a look at it.

--kiloran

But the problem is I haven't changed anything, Scripts /python folders are copied, manifest file is copied or 3 rows added, so why is the scrape seeing the macro but my own isn't?

I extracted the spreadsheet files using 7 zip and now have all these folders which look identical between my spreadsheet and the scrape one. The worksheet name is identical.

Thanks for the offer.

I just checked my methodology as follows, using Windows 7 and LibreOffice 5.4.3.2

1. I took an existing *.ods file which had no macros and renamed it test.ods
2. I added a sheet named Sheet1
3. Opened it as an archive using 7-zip
4. Added a folder named Scripts, and opened the folder
5. Added a folder named python and opened the folder
6. Opened my SimpleYahooPriceScrape_1-3a.ods using 7-zip and extracted the python script SimpleYahooPriceScrape.py to my desktop
7. Added SimpleYahooPriceScrape.py to my test.ods, in the Scripts/python folder
8. Opened the folder META-INF and copied manifest.xml to my desktop
9. Edited manifest.xl using notepadd++ by adding the 3 lines of code shown below, after the row ending "application/vnd.oasis.opendocument.spreadsheet", and saved manifest.xml
10. Copied/Replaced manifest.xml in the META-INF folder
11. Closed test.ods archive
12. Opened test.ods and typed SSE.L in cell A2
13. TOOLS/MACROS/ORGANISE MACROS/PYTHON
14. Click on the + symbol to the left of test.ods
15. Select UpdateYahooPricePy and click on Run
16. Cells B2 and C2 are populated with data

Added rows in manifest.xml

<manifest:file-entry manifest:full-path="Scripts/python/SimpleYahooPriceScrape.py" manifest:media-type=""/>
 <manifest:file-entry manifest:full-path="Scripts/python/" manifest:media-type="application/binary"/>
 <manifest:file-entry manifest:full-path="Scripts/" manifest:media-type="application/binary"/>

If it is not working for you, I suspect you have a typing problem and not being VERY strict with upper and lower case. When you edit the xml file, are you using a simple text editor such as Notepad? Do NOT use a word processor such as Word or Writer.

Hope this helps

--kiloran

[JMN2]

KIloran, I used notepad and wordpad. Also is archiving same as extracting, which is what I did to see the folders. Also I just copied the whole scripts folder with python folder in it accross, I did not add and open as in your #4 and #5.

I will try to do it exactly as you instructed, thanks for your help.

I suspect I am not archiving in 7zip correctly, do I have to work inside 7zip using the folders created? (I didn't, I just opened a file manager)

[JMN2]

So if I "extract" I get a new folder with all those files in them. Can I then work on those files outside 7 zip ie in file folder or where ever and as long as I amend the proper files and create new folders things should work?

Why would I copy a file to desk top and amend it there when I can copy it directly to the final destination and amend it there?

I suspect I am nor archiving (extracting?) properly even though I get the same folders as the scrape one.

The programme did not allow me to extract script file to desktop. I already see the file, can't I just copy it over to the right place?

[JMN2]

DONE!!!!!!!!!!!! HUGE THANKS TO KILORAN FOR HIS HELP AND PATIENCE.

Problem was, I am a total novice, and extracting the files and working on them, instead of archiving and saving in the archive --- what ever that means I still have no idea but as soon as I started working inside 7 zip and archiving and when I got a prompt do you want to save the file in the archive I knew I was close. So just amending extracted files will not make the changes stick even though files look correct, needs updating and amending the archive.

Only took me 24 hours and now I will have a beer even though it is not Friday.

Thanks again, you're a star.