Script not working

Mat

I downloaded the latest v 2.4 and the script.
I get the following message:


Help please

VidasMatelis

Mat,

First make sure that you started Internet Explorer at least once and accepted settings.
Then go to finance.yahoo.com website and request quote for some common stock and check History link - do you see quotes without problems? If all above is good, then go to Scripts\Log folder and post here first 15-20 lines from Log\GetQuotes-Yahoo.txt file - this is a log file, not a script file, and will have some info about requested data.

Mat

I can get a quote using Internet Explorer from Yahoo Finance.
Here are the log file on GetQuote:

# 2017-Sep-10. Created by Maxim T.
$scriptPath = Split-Path -parent $MyInvocation.MyCommand.Definition;
. ($scriptPath + "\psFunctions.ps1"); # Adding script with reusable functions
. ($scriptPath + "\psSetVariables.ps1"); # Adding script to assign values to common variables

$logFile = $scriptPath + "\Log\" + $MyInvocation.MyCommand.Name.Replace(".ps1",".txt");
(Get-Date).ToString("HH:mm:ss") + " --- Starting script " + $MyInvocation.MyCommand.Name | Out-File $logFile -Encoding OEM; # starting logging to file.
$logSummary = (Get-Date).ToString("HH:mm:ss") + " Script: Yahoo Historical".PadRight(28);
$quoteIDFile = $dataRootFolder + "\QuotesIntraDay\YahooIntraday.txt"; if (Test-Path $quoteIDFile) { Remove-Item $quoteIDFile;} # Removing intraday file before each load

$listStart = $config.IndexOf(""); $listEnd = $config.IndexOf("");
if ($listStart -eq -1 -or $listEnd -eq -1 -or $listStart+1 -ge $listEnd) {" Symbol list is empty. Exiting script." | Out-File $logFile -Encoding OEM -Append; exit(1);}
$symbolList = @($config | Select-Object -Index(($listStart+1)..($listEnd-1))); #list of symbols we will work on
"Symbol count: " + $symbolList.count + ". MinDate: $minDate" | Out-File $logFile -Encoding OEM -Append;

# From 2017-05 Yahoo requires session with cookie and crumb value at the end of URL to return data.Example: https://query1.finance.yahoo.com/v7/finance/download/VYM?period1=1500137940&period2=1502816340&interval=1d&events=history&crumb=nF2PUWr9OBA
$wrCookie=""; $wr=""; $crumbStr="`"CrumbStore`":{`"crumb`":`""; $crumb = "";
$urlCookie = "https://finance.yahoo.com/quote/AAPL/history?p=AAPL"; #This URL will be used to get establish session and get crumb value
for ($i=0;$i -le 2 -and $crumb -eq ""; $i++) { # will attempt to get crumb 3 times
$wrCookie = Invoke-WebRequest -Uri $urlCookie -SessionVariable websession; # Session with cookie now is in $websession variable.
$webCookie = $wrCookie.Content; # Need to parse content and look for string $crumbStr value.
$crumbStart = $webCookie.IndexOf($crumbStr); if ($crumbStart -eq 0) {"Crumb start not found" | Out-File $logFile -Encoding OEM -Append; continue;} #Identifing crumb location in file. Get start location of the value;
$crumb = $webCookie.Substring($crumbStart+$crumbStr.Length,100); # Get 100 characters from the crumb start
$crumbEnd = $crumb.IndexOf("`"}"); if ($crumbEnd -eq 0) {"Crumb end not found" | Out-File $logFile -Encoding OEM -Append; continue;} # Find crumb string end.
$crumb = $crumb.Substring(0, $crumbEnd); # ================ At this point we have crumb value in $crumb and web session with cookie established in $websession;
if (-not($crumb -match "^[a-zA-Z0-9\s]+$") -and $i -ne 2) {$crumb = "";}
}

$urlBase = "https://query1.finance.yahoo.com/v7/finance/download/@Symbol@?period1=@FromDay@&period2=@ToDay@&interval=1d&events=history&crumb=@CRUMB@"
$toDay = [string] [math]::Floor((get-date -UFormat %s)); #today in unix timestamp
$urlBase = $urlBase.Replace("@CRUMB@", $crumb).Replace("@ToDay@", $toDay);
ForEach($sLine in $symbolList) { # For each symbol
$ret = GetSymbolInfo $sLine $quotesFolder $minDate; $symbol = $ret[0]; $nextDate = $ret[1]; $symbolMaxDate = $ret[2]; $symbolMaxDateAdj = $ret[3]; $symbolQuoteFile = $ret[4]; $lastQuote = $ret[5];
"Symbol: $symbol. Next date: $nextDate. Quote file: $symbolQuoteFile" | Out-File $logFile -Encoding OEM -Append;
if ($nextDate -gt $symbolMaxDate) { " We already have data up to maximum configuration date of $symbolMaxDate. Will not request new data for this symbol." | Out-File $logFile -Encoding OEM -Append; continue; }

VidasMatelis

Hi,

You posted here script file and not the log file. In the same folder where there is a script file (.ps1) there is a log subfolder with .txt files that are log files. Can yo post here first 10-20 lines of LOG file.

Mat

Here you go. Thanks in advance for your help!

00:46:23 --- Starting script GetQuotes-Yahoo.ps1
Symbol count: 100. MinDate: 2014-12-31
Symbol: ^GSPTSE. Next date: 2014-12-31. Quote file: C:\Users\wongm\OneDrive\Documents\Documents\Portfolio Slicer 2.4\PortfolioSlicerDDMScriptsV2.4.12\Scripts20190804\Quotes\__GSPTSE_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/^GSPTSE?period1=1419984000&period2=1585442785&interval=1d&events=history&crumb=
^GSPTSE - Not Found (web err)

Symbol: ^GSPC. Next date: 2014-12-31. Quote file: C:\Users\wongm\OneDrive\Documents\Documents\Portfolio Slicer 2.4\PortfolioSlicerDDMScriptsV2.4.12\Scripts20190804\Quotes\__GSPC_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/^GSPC?period1=1419984000&period2=1585442785&interval=1d&events=history&crumb=
^GSPC - Not Found (web err)

Symbol: XEI.TO. Next date: 2014-12-31. Quote file: C:\Users\wongm\OneDrive\Documents\Documents\Portfolio Slicer 2.4\PortfolioSlicerDDMScriptsV2.4.12\Scripts20190804\Quotes\_XEI.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/XEI.TO?period1=1419984000&period2=1585442785&interval=1d&events=history&crumb=
XEI.TO - Not Found (web err)

Symbol: VAB.TO. Next date: 2014-12-31. Quote file: C:\Users\wongm\OneDrive\Documents\Documents\Portfolio Slicer 2.4\PortfolioSlicerDDMScriptsV2.4.12\Scripts20190804\Quotes\_VAB.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/VAB.TO?period1=1419984000&period2=1585442785&interval=1d&events=history&crumb=
VAB.TO - Not Found (web err)

Symbol: ZDI.TO. Next date: 2014-12-31. Quote file: C:\Users\wongm\OneDrive\Documents\Documents\Portfolio Slicer 2.4\PortfolioSlicerDDMScriptsV2.4.12\Scripts20190804\Quotes\_ZDI.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/ZDI.TO?period1=1419984000&period2=1585442785&interval=1d&events=history&crumb=
ZDI.TO - Not Found (web err)

Symbol: RIT.TO. Next date: 2014-12-31. Quote file: C:\Users\wongm\OneDrive\Documents\Documents\Portfolio Slicer 2.4\PortfolioSlicerDDMScriptsV2.4.12\Scripts20190804\Quotes\_RIT.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/RIT.TO?period1=1419984000&period2=1585442785&interval=1d&events=history&crumb=
RIT.TO - Not Found (web err)

Symbol: XSH.TO. Next date: 2014-12-31. Quote file: C:\Users\wongm\OneDrive\Documents\Documents\Portfolio Slicer 2.4\PortfolioSlicerDDMScriptsV2.4.12\Scripts20190804\Quotes\_XSH.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/XSH.TO?period1=1419984000&period2=1585442785&interval=1d&events=history&crumb=
XSH.TO - Not Found (web err)

Symbol: VEE.TO. Next date: 2014-12-31. Quote file: C:\Users\wongm\OneDrive\Documents\Documents\Portfolio Slicer 2.4\PortfolioSlicerDDMScriptsV2.4.12\Scripts20190804\Quotes\_VEE.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/VEE.TO?period1=1419984000&period2=1585442785&interval=1d&events=history&crumb=
VEE.TO - Not Found (web err)

Symbol: VXC.TO. Next date: 2014-12-31. Quote file: C:\Users\wongm\OneDrive\Documents\Documents\Portfolio Slicer 2.4\PortfolioSlicerDDMScriptsV2.4.12\Scripts20190804\Quotes\_VXC.TO_.txt
Requesting url: https://query1.finance.yahoo.com/v7/finance/download/VXC.TO?period1=1419984000&period2=1585442785&interval=1d&events=history&crumb=
VXC.TO - Not Found (web err)

VidasMatelis

Hi Mat,

I looked at the script code (it is not mine, it was build by Maxim) and compared your log with mine. As you can see from the log, when requesting URL is built, it supposed to have crumb value assigned. In your case crumb value is empty. I am not sure why value for you is empty, so here are my best guests.
1. Please check PowerShell version on your PC. Are you running latest version of Windows 10? Can you start cmd, type "PowerShell" to enter PowerShell environment and then type "(get-Host).version". Is major version of PowerShell "5" ?
2. Yahoo blocked your computer from getting quotes. This happens when you request too many quotes to often and, from what I read, usually last 3-4 days. Did you run lots of quote request from yahoo lately?

I double checked that for me the same script works and for many other users it works too. What else is different for you? Did you run this script before and it stopped working suddenly, or are you a new user? Any more info you can share that could help to identify why are your PC different?

Mat

The script has worked in my older computer running Windows 7 and Office 2010.
I have upgraded to a new computer running Microsoft 365 and the script stopped working.

Any suggestion for upgrading Portfolio Slicer to my new computer?
I have tried to read your documents on the website for upgrading and I encounter issues I couldn't resolve myself.

Is it better to start from scratch as a new install in the new computer?
Then I can copy the transaction data from the old worksheet.

Thanks.

VidasMatelis

On the new computer most likely error is - Internet Explorer was not started, so PowerSHell cannot initiate Internet Explorer "session". So start Internet Explorer, accept configuration settings and then run script. If you are still getting an error, then post here first few lines from the first script that was executed log, that is located in Script\LOG subfolder!
If you are migrating from Excel 2010, then first make a backup of your Excel workbook, then you can try opening workbook in Office 365. You will get a message suggesting converting your workbook to new version. If after conversion you can refresh your workbook - then you are OK. But if you will get any error message, then you should copy your source data to new workbook for Excel 2016 - it has a bit more efficient code for some calculations.

Mat

Thanks again for your help!
I tried to run the script again. It seems to be better than before, but I still see error messages. Below are the excerpts.

I have tried to copy the old script to the new computer. May be I should start from scratch to reinstall the script?

VidasMatelis

Message says that Internet Explorer engine is not available. Did you start manually Internet Explorer and accepted configuration settings?

Mat

I use Microsoft Edge. Is it the same as Internet Explorer?

VidasMatelis

No, Internet Explorer is different. It is used by PowerShell. YOu do not have to use Internet Explorer, but you have to start it first time - it will ask you questions about setup and after you done, you dont have to every start it again, but you must start it first time and go through setups, so after that PowerShell scripts from PortfolioSlicer would work.

Mat

The script works, thanks. However, I run into another problem while trying to refresh.
Please take a look at below.

Mat

Just to remind you, I moved from Excel 2010 in my old computer to my new computer running Office 365. Not sure if it makes a difference.

Mat

I went into the Power Pivot, "Existing Connection", I see the above.

VidasMatelis

Your error is "Circular dependency was detected". I have seen in the past this error when there was a problem with data in Transactions table - specifically when there was sell transactions before buy transactions. Could you please review Transactions data and make sure that QtyHeld values are NOT NEGATIVE at the end of the day.
I am assuming that you opened your Excel 2010 file in Office 365, was asked to do conversion and did that and after, when you tried to refresh, you got an error message. I would do following:
1. Make sure that first System transactions stays in place - do not delete it.
2. Order srcTrans worksheet by account and remove (copy to another worksheet) all, but one account transactions and try to refresh. If you are not successfull, try another account.
3. Start adding different account transactions one by one and refresh. If refresh fails, review closely transactions that you added.