›

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Sign In Register

Quick Links

Categories

Help - no source for stock prices!

le4rle4r
in External Data Files
Hi Vidas

Noting the recent conversation about Yahoo no longer supplying stock price data, I'm wondering (concerned!!) that google has done similar - or has at least moved it.

I have tried inputting a number of UK stocks (prefaced LON:) into google finance and the tab 'historic prices' has now disappeared. I did a web search on this and retrieved the following article which summarises the issue, albeit to the S&P index : https://productforums.google.com/forum/#!topic/docs/o1jgHoR4y_k;context-place=topicsearchin/docs/category$3Aspreadsheets.

Google finance is still displaying the closing prices for my stocks, so maybe the info is available, but it's not obvious where.

Any ideas as to how to retrieve this and get the update script to work would be very welcome!!

Thanks in advance

Laurence

«13

Comments

  • VidasMatelisVidasMatelis
    Hi Laurence,

    Can you please give sample of stock that you are experiencing problems with?
    I contacted Maxim about extracting data from Yahoo. I was told that it is possible to do, just the way extract can be done changed, so he needs some time to look into this. I hope that we will get back option to get quotes from Yahoo soon.
  • le4rle4r
    Hi Vidas

    All the tickers I check have this issue. By way of example:

    LON:BOO
    LON:GENL
    LON:LLOY

    (The page I'm on is https://www.google.com/finance?q=LON:LLOY&ei=IyKoWYjWGoeUUoHwoogC)

    Incidentally, I note that while the historic prices tab is missing on google finance, the UpdatePSData script successfully downloaded closing prices for a number of my stocks, but not for all. In the latter case, I had many days where the closing price seemingly froze, giving entirely incorrect portfolio values etc. Example set out at the bottom of this message. I cannot tell what has caused this and why some quotes pulled through correctly and others didn't?

    Thanks

    Laurence


    ----

    Example of a 'stuck' Quote file (see the repetitive 'closing price' from 2017-06-30 onwards)

    2017-04-28,3.40,LON:TAVI
    2017-05-31,3.47,LON:TAVI
    2017-06-30,3.73,LON:TAVI
    2017-07-11,3.73,LON:TAVI
    2017-07-12,3.73,LON:TAVI
    2017-07-13,3.73,LON:TAVI
    2017-07-14,3.73,LON:TAVI
    2017-07-17,3.73,LON:TAVI
    2017-07-18,3.73,LON:TAVI
    2017-07-19,3.73,LON:TAVI
    2017-07-20,3.73,LON:TAVI
    2017-07-21,3.73,LON:TAVI
    2017-07-24,3.73,LON:TAVI
    2017-07-25,3.73,LON:TAVI
    2017-07-26,3.73,LON:TAVI
    2017-07-27,3.73,LON:TAVI
    2017-07-28,3.73,LON:TAVI
    2017-07-31,3.73,LON:TAVI
    2017-08-01,3.73,LON:TAVI
    2017-08-02,3.73,LON:TAVI
    2017-08-03,3.73,LON:TAVI
    2017-08-04,3.73,LON:TAVI
    2017-08-07,3.73,LON:TAVI
    2017-08-08,3.73,LON:TAVI
    2017-08-09,3.73,LON:TAVI
    2017-08-10,3.73,LON:TAVI
    2017-08-11,3.73,LON:TAVI
    2017-08-14,3.73,LON:TAVI
    2017-08-15,3.73,LON:TAVI
    2017-08-16,3.73,LON:TAVI
    2017-08-17,3.73,LON:TAVI
    2017-08-18,3.73,LON:TAVI
    2017-08-21,3.73,LON:TAVI
    2017-08-22,3.73,LON:TAVI
    2017-08-23,3.73,LON:TAVI
    2017-08-24,3.73,LON:TAVI
    2017-08-25,3.73,LON:TAVI
    2017-08-29,3.73,LON:TAVI
    2017-08-30,3.73,LON:TAVI

  • VidasMatelisVidasMatelis
    Hi,

    I see that Historical Prices link for these stocks is gone. Sorry, I cannot do much - we will have to wait till Maxim will update script to extract data from Yahoo.
  • VidasMatelisVidasMatelis
    Hi,

    I just updated scripts (thanks to Maxim!) and now support for Yahoo quotes is back. Please check download page for latest scripts!
  • le4rle4r
    Thank you both Vidas and Maxim for on-going work to keep this excellent product working despite Google and Yahoo's intent to play around with historic stock price data!

    I am about to update all of my files but have a few questions:

    1) Like Maffs0, I am a UK trader, so all my symbols change from LON:XXX to XXX.L when I switch back to Yahoo. I saw your comments to Maffs0 about how to deal with this but my psConfig.txt file does not have the SymbolRename section you refer to - can you provide the code and tell me where to add it?

    2) To make the update, is it just a case of downloading the latest script files and archiving the old symbol quotes (per your advice in post 1 of http://support.portfolioslicer.com/discussion/185/switching-quotes-source-from-yahoo-to-google/p1)?
    - Will the SymbolRename section take care of the LON:XXX format in srcSymbol sheet? Or do I need to update columns A, M and U in srcSymbol sheet?
    - I was receiving error messages when trying my final google UpdatePSData.bat runs - to do with max / min dates in the file and duplicate records? - should I replace / redownload any files beyond the latest scripts?

    3) Can I change the MinDate at the same time (I have found some old trades I would like to add, so would like to make the MinDate earlier than it is presently)?

    Sorry - trying to explain my questions as best I can, but not a computer programmer or IT wiz!

    Thanks in advance

    Laurence
  • VidasMatelisVidasMatelis
    edited September 2017
    If you downloaded latest scripts, they have updated psConfig.txt sections. But you can simply add section like this:
    <SymbolRename>
    #TSE:XIU,XIU
    </SymbolRename>

    When you switch back to Yahoo you need to do rename:
    XXX.L, LON:XXX
    Then in your Excel workbook you can keep using LON:XXX as symbol.

    So backup your quotes before doing any changes. Some quotes might not be available anymore! I would do following:
    1. Backup all quotes.
    2. Delete all files from Quotes folder
    3. Change min date parameter in psConfig.txt
    4. Re-extract quotes. Check each quote file and make sure you have all data. If no, then you might need to combine data from old backup with new files.
    5. Update MinDate in Excel workbook, review transactions - like first deposit should be on MinDate, etc
    6. Refresh.
    7.
  • le4rle4r
    Hi Vidas

    1. I have tried to do the update as suggested, but received the following message when re-extracting quotes (attached). (Note that I used the very latest scripts that Maxim posted on 16/09 on the 'Scripts to get...' thread, available here https://www.dropbox.com/s/qesvuvzdki600cl/Scripts20170916.zip?dl=1.) Could you advise what it means and what action to take please?

    2. Despite the above message, many quotes have been downloaded, but a number have only one year's worth of data. I note you say (point 4 above) to combine data with old backup - how exactly does one do this? Is this what the SymbolRename allows me to do - having both google and yahoo quote files in the Quote folder, for different time sequences?

    3. One of my stock analysis tools provides a lot of historic price data - will it work if I download that info and convert into yahoo format (in excel) and then paste directly into my yahoo quote files?

    4. As a side point, I'm afraid that I couldn't follow how the SymbolRename works, so I decided to change all of my symbols in the psConfig.txt file back to XXX.L format when moving from the google list to yahoo list, and I can make consequent change to columns A, M and U of srcSymbol sheet. (No change needed to srcTrans because I work with symbolalias XXX). At some point maybe you could post a quick video to explain how this symbol rename works and how to convert between google and yahoo and vice versa?

    Sorry again to take up your precious time - I really appreciate your efforts here. This is a fantastic product that I have come to love using... just so upsetting that after painstaking time to get it to work first time around, I'm now locked out because of my lack of understanding of how exactly the scripts work and how precisely to finish the new configuration of all of my data!

    Incidentally, do you have a 'contributions' feature? I'd happily donate in respect of the product and your ongoing assistance!

    Cheers

    Laurence
  • VidasMatelisVidasMatelis
    Laurence

    1. Your error is just in Intraday currency exchange extract script. This issue is already fixed in updated script from download page. Direct link to get updated scripts: http://www.portfolioslicer.com/download/v2.3/PortfolioSlicerDDMScriptsV2.3.1.zip. So this issue has nothing to do with any other issues you are having.
    2. Most likely your data source is google and just last week google started to allow download just 1yrs of data. Your best option would be to see if you can get data from different source - Yahoo, AlphaVantage or Stooq. If that does not work, then take old backup you have and if necessary do symbol renames and the run extract. Example, you have old file Quotes\_xxx.l_.txt that has quotes for XXX.L symbol. You now are trying to get quotes for symbol LON:XXX, so new created file will be Quotes\_lon_xxx_.txt. You copy older records from file Quotes\_xxx.l_.txt to file Quotes\_lon_xxx_.txt and after that do global replacement in that file for XXX.L to LON:XXX. I will create more documentation to explain how data is stored, but if you will poke around in Quotes folder, you will see very simple storage structure. If you have symbol example, post here what you trying to do (old source, new source, old symbol, new symbol), and I'll try to explain.
    3. Check files in Quotes folder - open them with notepad. You will see that there is one records per day. Ignore _archive file - this will be created for you automatically. So extract quotes data from your other source into excel, adjust column order and save in csv format. Then edit file and if necessary remove header - you will notice that files in Quotes folder do not have headers.
    4. I will provide more documentation - I realized I need that. But SymbolRename works like this - if you add line "ABC,XYZ", all records for symbol ABC will be renamed to symbol XYZ in final quotes.csv file.

    Thank you for donation offer, but I choose not to accept them :). My business model is "advertising based revenue", so just make sure that if you use adblocker - please disable it on this website.

    Regards,
  • le4rle4r
    Hi Vidas

    Some progress I think!

    1. Bingo - thanks

    2. Tried AlphaVantage but wasn't sure how to extract quotes as it does not seem to use XXX.L or LON:XXX format for symbols, like Yahoo and Google, respectively. Also, it appears to need an API key - a grade above my competence as I do not know how to embed this detail into the config file format!

    Stooq seems to have given a partial fix for certain quotes where yahoo did not retrieve anything.

    To complete missing data, I will do manually as you suggest - I follow the logic structure, so not difficult with excel and find/replace.

    3. Makes sense - thanks.

    4. Thanks

    And a few extra points to check:

    5. Ref. 2 above, some data provider's historic data is incorrect, not just missing. If I manually compile a quote file by piecing together historic info from back-ups / my other sources, when I re-run the update psdata script to download latest price information from yahoo, will it just add the new data sequentially to the bottom of the quote file (i.e. not override the historic info above that I have manually inserted)?!

    6. Could I use different stock quote files for different time periods? So, if I have accurate historic google information in my backup files, then I could have Quotes\_lon_AAA_.txt until a certain date and then Quotes\_AAA.l_.txt for all future dates (updated with updatepsdata.dat)? If I now understand the SymbolRename correctly, adding the lines AAA.L,AAA and LON:AAA,AAA would mean that my portfolio slicer file would load both sets of quotes - covering the different timeperiods - if I use the symbol AAA within the workbook?

    7. Once a stock has been sold entirely (units = zero), I assume that the symbol can be removed from the data provider's symbol list in psConfig.txt file (while leaving the quote file in situ of course). Is that correct? (My symbol lists are getting long and cluttered with historic holdings.)

    8. Do I need to do anything to my C:\PortfolioSlicer\PSData records? The Quote.csv file there will not match the content of the Quotes files, due to variations between the different data providers.

    Thanks for your continued patience and help.

    Laurence
  • VidasMatelisVidasMatelis
    Hi Laurence,

    5. When you run scripts, then data in quotes folder is never deleted - just appended. So lets say you run .bat file and you have symbol ABC. These scripts first will try to find file Quotes\_ABC_.txt and if that file found then will read data, order by date and take latest date (order stored in the file does not matter). Then just new quotes will be requested from the web. So if you will edit data in Quotes folder, they will not be changed next time you run scripts.
    6. You are right, you can use different quote files and they ALL will be merged into one final Quotes.csv file. Just make sure that your dates do not overlap after renaming.
    7. Yes, you can remove symbol from psConfig.txt file.
    8. all files in psData folder are completely recreated during each .bat run - you do not need to do anything there.
  • le4rle4r
    Thanks Vidas. all makes sense.

    I will report back when done - hopefully successfully.
  • VidasMatelisVidasMatelis
    If you have any more questions please do not hesitate to ask here. I am starting to write documentation on how Maxim scripts stores/retrieves data, but I am usually slow on documenting stuff.
  • le4rle4r
    Ok, great - thanks a lot, really appreciate it.
  • le4rle4r
    Hi Vidas

    I have managed now to extract all of my historic price data from the stock analysis tool I mentioned and manipulate each file into the correct storage structure. I did this for all holdings (existing and historic, up to 2017-10-04). In my psconfig.txt file I have then reduced my yahoo list of symbols to just the current holdings and have used 2017-10-05 as the min date to avoid the overlap as you mentioned (see attached).

    I carefully checked all quote files that I had created, and then ran the updatepsdata.bat file to refresh all downloads. When I did this, I received an error messages (attached), suggesting that Yahoo was trying to retrieve the quotes for my current holdings for 2017-10-04, despite the min date above. What is going on and how do I resolve it please?

    Thanks

    Laurence
  • VidasMatelisVidasMatelis
    Lets deal with one symbol - ATYM.L.
    If you go to \Quotes folder you will see that there is no file _ATYM.L_.txt where you would save daily quotes. But if you will go into \QuotesIntraDay\ folder, you will see that intraday quotes for that symbol were extracted and are placed into YahooIntraday.txt file. So if you will not do anything and tomorrow you will re-run script - all will work without problems. I believe there is a bug in YahooIntraday script that checks just for SymbolMaxDate and if it is not specified, then that symbols intraday quote is extracted. I have not seen the case yet when SymbolMinDate would be in the future.
    So again, I believe if you will run same script tomorrow, you will not get any issues.
    If you want to run now, you can edit .bat files and in front of "Intraday scripts" add "REM " (remark - so comment out).That is:
    1. Copy existing UpdatePSData.bat to UpdatePSData2.bat.
    2. Edit UpdatePSData.bat to be: 
    echo off
powershell -ExecutionPolicy Bypass .\Scripts\GetQuotes-Yahoo.ps1
REM powershell -ExecutionPolicy Bypass .\Scripts\GetQuotes-YahooIntraday.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetQuotes-Google.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetQuotes-GoogleWeb.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetQuotes-AlphaVantage.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetQuotes-Stooq.ps1
REM powershell -ExecutionPolicy Bypass .\Scripts\GetQuotes-GoogleIntraday.ps1
REM powershell -ExecutionPolicy Bypass .\Scripts\GetExchRates-BoC.ps1
REM powershell -ExecutionPolicy Bypass .\Scripts\GetExchRates-ECB.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetExchRates-Stooq.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetExchRates-YahooIntraday.ps1
powershell -ExecutionPolicy Bypass .\Scripts\GetDividends-Yahoo.ps1
powershell -ExecutionPolicy Bypass .\Scripts\psArchiveOldQuotes.ps1
powershell -ExecutionPolicy Bypass .\Scripts\psMakeAllDataFiles.ps1
powershell -ExecutionPolicy Bypass .\Scripts\psCreateDatesFile.ps1
powershell -ExecutionPolicy Bypass .\Scripts\psAppendGeneratedQuotes.ps1
powershell -ExecutionPolicy Bypass .\Scripts\psCheckFiles.ps1
choice /C Y /T 10 /D Y /M "Waiting 10sec before closing"
    3. Run UpdatePSData.bat and check that there are no errors. This will delete intraday files, so error should dissapear.
    4. Tomorrow, after markets are open for at least 20min, copy back UpdatePSData2.bat to UpdatePSData.bat

    Let me know if you still have any issues.
Sign In or Register to comment.