Simple Yahoo Price Scrape update
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Simple Yahoo Price Scrape update
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:
https://www.lemonfool.co.uk/viewtopic.php?f=27&t=8332
--kiloran
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:
https://www.lemonfool.co.uk/viewtopic.php?f=27&t=8332
--kiloran
-
- Lemon Slice
- Posts: 761
- Joined: November 19th, 2016, 1:35 pm
Re: Simple Yahoo Price Scrape update
Hi Kilorankiloran wrote: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"
The B & C column pre-populated are then blank
A huge thanks for all your work in overcoming the Yahoo change
Mike
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: Simple Yahoo Price Scrape update
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.mike wrote: 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
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
-
- Lemon Slice
- Posts: 761
- Joined: November 19th, 2016, 1:35 pm
Re: Simple Yahoo Price Scrape update
Does !kiloran wrote: .... uses the same code as HYPTUSS b-f-dev4 which does(did??) work for you a few days ago.
--kiloran
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
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: Simple Yahoo Price Scrape update
Mike, I've fixed the problem, and published version 1-3a at: http://lemonfoolfinancialsoftware.weebl ... crape.htmlmike wrote:Sorry to let you know, but this doesn't work for me.kiloran wrote: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
Open Office 4.1.3, Windows 7 professional
Error message is "No data returned from Yahoo"
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
-
- Lemon Slice
- Posts: 761
- Joined: November 19th, 2016, 1:35 pm
Re: Simple Yahoo Price Scrape update
Thanks Kiloran, all fine now (OO 4.1.4, W8.1)kiloran wrote: 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
Sorry, don't have Excel so can't check out iaag's version.Itsallaguess has worked his magic to overcome the Yahoo change and the Excel version 1-1 is also published at the above link
Really appreciate your work, thank you
Mike
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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?
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?
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: Simple Yahoo Price Scrape update
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.JMN2 wrote: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.
There, I said it was obvious
It seems permanent according to my very cloudy crystal ball. Until tomorrow. Or the day after. Quite simply.... nobody knows.JMN2 wrote: 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?
Shout if you need more help on the python bits and pieces
--kiloran
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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?kiloran wrote:...
Shout if you need more help on the python bits and pieces
--kiloran
Ie would it be obvious where to find the corresponding place and copy over?
Yes, I don't have a clue...
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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...
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...
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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.
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.
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: Simple Yahoo Price Scrape update
No, there's no compiling required with python.JMN2 wrote: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...
Here's a few things you need to do....
- The python script is designed to work with data in a sheet named Sheet1. Case sensitive
- The python script must be in a folder Scripts/python. Case sensitive.
- 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>
Hope this helps.
--kiloran
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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.
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.
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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.
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.
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: Simple Yahoo Price Scrape update
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.JMN2 wrote: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.
--kiloran
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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?kiloran wrote: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.JMN2 wrote: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.
--kiloran
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.
-
- Lemon Quarter
- Posts: 3865
- Joined: November 4th, 2016, 9:24 am
Re: Simple Yahoo Price Scrape update
I just checked my methodology as follows, using Windows 7 and LibreOffice 5.4.3.2JMN2 wrote: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?kiloran wrote: 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
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 took an existing *.ods file which had no macros and renamed it test.ods
- I added a sheet named Sheet1
- Opened it as an archive using 7-zip
- Added a folder named Scripts, and opened the folder
- Added a folder named python and opened the folder
- Opened my SimpleYahooPriceScrape_1-3a.ods using 7-zip and extracted the python script SimpleYahooPriceScrape.py to my desktop
- Added SimpleYahooPriceScrape.py to my test.ods, in the Scripts/python folder
- Opened the folder META-INF and copied manifest.xml to my desktop
- 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
- Copied/Replaced manifest.xml in the META-INF folder
- Closed test.ods archive
- Opened test.ods and typed SSE.L in cell A2
- TOOLS/MACROS/ORGANISE MACROS/PYTHON
- Click on the + symbol to the left of test.ods
- Select UpdateYahooPricePy and click on Run
- Cells B2 and C2 are populated with data
<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
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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)
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)
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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?
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?
-
- Lemon Quarter
- Posts: 2169
- Joined: November 4th, 2016, 11:21 am
Re: Simple Yahoo Price Scrape update
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.
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.