Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Market Index 1 return%
Hi there... This is, by far, the best portfolio management tool that I have come across - paid or not. Awesome job and thanks for all your effort.
I have managed to pull all my transactions into the excel and am able to run the scripts successfully. There is one issue that I see -
Issue 1: Index 1% return is not accurate
I have ^NSEI and ^BSESN indices configured as per instructions (in src: Local Config, srcSymbol: all 3 tables and in the psconfig.txt under Yahoo section. The scripts pull all the quote data including both the indices. I have checked the quotes.csv and see that both the indices data are present. The issue is this - whereever the return is compared against the indices, Index 1% is always 100% and for all time periods. Index 2% is fine. Could you please help? BTW, Index 1 refers to ^NSEI.
Minor code fix: UpdatePSData.bat scripts fail if there are files in the Quotes folder
This is a simple fix. I just added a line - del /q C:\PortfolioSlicer\Quotes\*.*. This fixed the issue.
I have managed to pull all my transactions into the excel and am able to run the scripts successfully. There is one issue that I see -
Issue 1: Index 1% return is not accurate
I have ^NSEI and ^BSESN indices configured as per instructions (in src: Local Config, srcSymbol: all 3 tables and in the psconfig.txt under Yahoo section. The scripts pull all the quote data including both the indices. I have checked the quotes.csv and see that both the indices data are present. The issue is this - whereever the return is compared against the indices, Index 1% is always 100% and for all time periods. Index 2% is fine. Could you please help? BTW, Index 1 refers to ^NSEI.
Minor code fix: UpdatePSData.bat scripts fail if there are files in the Quotes folder
This is a simple fix. I just added a line - del /q C:\PortfolioSlicer\Quotes\*.*. This fixed the issue.
Comments
For Index 1 to work you must:
1. Add index to psConfig.txt file.
2. Add index to src worksheet Config table MarketIndex1 column (C4).
3. Add index to Symbol table in worksheet srcSymbol.
Based on your post, it appears that you already done this. Even more, it appears that you made it work with Index 2. So I suspect that issue could be with that specific symbol - maybe data you are getting back is not OK (always same value?), maybe currency in srcSymbol table is not right? Try changing that index to another symbol and see if that works. Or maybe somewhere you index specified has extra spaces at the start or end (check Config table).
YOu can also send me your Excel workbook and I could investigate. For privacy you can remove your trasanctions, just make sure that after removing you refreshed data and you still can see issue. My email is posted on this page: http://www.portfolioslicer.com/contact.html
You reported issue that UpdatePSData.bat script fails if there are files in the quotes folder - that indicates to me that there is something not right with your updates. Idea is that Quotes files contains data that you once downloaded so you do not need to re-download data. This makes quote download process much faster and ensures that if you once got data, it is yours, even when provider will no longer provide data. For example, you cannot anymore download DELL data as this symbol does not exists anymore, but I need it for my PortfolioSLicer as in the past I traded it. So you need to address this - you should not be deleting data from Quotes subfolder.
What error are you getting when you run script and do not delete data? What specific script and line creates that error?
On UpdataPSData.bat script and indices
Here is an update. I had ^NSEI as MarketIndex 1 and ^BSESN as MarketIndex 2 in Local Config. SymbolSector table had the same sequence. I switched them in src:Local Config and made ^NSEI as MI2 and ^BSESN as MI1 and now the numbers are showing up fine. No idea what happened there.
On the Quotes download - my understanding was that we would need to run the UpdatePSData.bat anytime we wanted updated quotes. For instance, after every trading day or even intraday. If the quotes are downloaded once already, would the script just download incremental updates? If so, I'll try this tomorrow, on a trading day and will update you. (I set this up during this long weekend and during non-trading days).
I ran it this morning once (Sunday, non-trading day) and the results are as below. Am attaching 2 of the GetQuotesYahoo.txt entries - 1 which succeeded in pulling data and 1 which didn't return data.
/================================================================================================
Found quote file. Looking for quotes starting from: 2017-03-16
Requesting url: http://ichart.finance.yahoo.com/table.csv?s=BEL.NS&a=2&b=16&c=2017&g=d&ignore=.csv
Added new quotes to file: C:\Users\HemadriD\OneDrive\PortfolioSlicer\Quotes\_BEL.NS_.txt
Done: BEL.NS (from: 2017-03-16). Record count: 22. Last quote: 2017-04-14
07:20:01 BEL.NS Last local quote: 2017-03-15. New rec count 22. Last quote: 2017-04-14
Found quote file. Looking for quotes starting from: 2017-04-15
Requesting url: http://ichart.finance.yahoo.com/table.csv?s=BERGEPAINT.NS&a=3&b=15&c=2017&g=d&ignore=.csv
BERGEPAINT.NS - Not Found (return empty file)
07:20:40 --- Finished. Quotes Requested/Succeed/Failed: 122/1/121. Duration: 46 sec.
=======================================================================================
I am glad that your Market index is now working. I believe it was just some extra space or some other small mistake. When you switched indexes and re-typed values it started to work.
Regarding Quotes:
You are right - you need to run this .bat file every time you want to get latest quotes. For example I schedule that task on my PC to run every hour during trading hours and then few more time over night to get up to date quotes without me executing anything. But I can do that because my PC is always ON. If your PC is not always ON, then you need to run this .bat file before you want to refresh your Excel workbook.
Output you posted does not have any errors and looks good.
Even if it shows that there are 121 Failed requests - that is all good as at the time of request additional quotes were not requested.
So if you are worry about "Failed" count - then you can ignore that. It is just important that you do not get and script execution errors - and based on your post you are not getting any errors.
So do not delete files before requesting new quotes - it is faster that way.
Tried running the scripts to get intraday updates during trading hours. However, am not seeing any intraday quotes. Here is the output of GetQuotesYahooIntraday.txt. Any thoughts on what's happening here? BTW, I tried running this on Google too and same result.
Thank you for all your timely help.
/======================================================================================================
Starting script. 2017-04-17 01:54:41
Data root folder: C:\Users\HemadriD\OneDrive\PortfolioSlicer
=======================================================================================
13:54:41 --- Starting script GetQuotesYahooIntraday.ps1. Symbol count: 122. MinDate: 2005-12-30
URL: http://download.finance.yahoo.com/d/quotes.csv?s=^BSESN,^NSEI,ADFFOODS.NS,ADORWELD.NS,ALBK.NS,ANDHRABANK.NS,ARVIND.NS,BAJFINANCE.NS,BALMLAWRIE.NS,BANKINDIA.NS,BAYERCROP.NS,BBL.NS,BEL.NS,BERGEPAINT.NS,BHARATFIN.NS,BHARTIARTL.NS,BHEL.NS,BIOCON.NS,BOSCHLTD.NS,BRITANNIA.NS,CANBK.NS,CAPF.NS,CASTROLIND.NS,CENTUM.NS,CENTURYTEX.NS,CIPLA.NS,CLNINDIA.NS,COALINDIA.NS,CONCOR.NS,CORPBANK.NS,COX&KINGS.NS,CROMPGREAV.NS,DALMIABHA.NS,ENGINERSIN.NS,EXCELCROP.NS,FEDERALBNK.NS,FINPIPE.NS,FRETAIL.NS,GABRIEL.NS,GANECOS.NS,GESHIP.NS,GLENMARK.NS,GRANULES.NS,GRAUWEIL.BO,GREAVESCOT.NS,GUFICBIO.NS,HAVELLS.NS,HCC.NS,HCLTECH.NS,HDFC.NS,HDFCBANK.NS,HMVL.NS,IDEA.NS,IDFC.NS,IDFCBANK.BO,INDNIPPON.NS,INFY.NS,IRB.NS,ISFT.NS,JBFIND.NS,JINDALPOLY.NS,JKTYRE.NS,JSWENERGY.NS,KPIT.NS,LICHSGFIN.NS,LT.NS,LUMAXTECH.NS,LUPIN.NS,M&M.NS,MAHABANK.NS,MANGCHEFER.NS,MANGLMCEM.NS,MANINDS.NS,MANINFRA.NS,MARUTI.NS,MOIL.NS,NATCOPHARM.NS,NBCC.NS,NESTLEIND.NS,NIITTECH.NS,NLCINDIA.NS,NTPC.NS,OCL.NS,ORIENTBANK.NS,PEL.NS,PENIND.NS,PERSISTENT.NS,PHILIPCARB.NS,PIDILITIND.NS,PNB.NS,PRIMAPLA.BO,DRREDDY.NS,RENUKA.NS,REPCOHOME.NS,SAIL.NS,SBIN.NS,SHANTIGEAR.NS,SHARDAMOTR.NS,SHOPERSTOP.NS,SONATSOFTW.NS,SQSBFSI.NS,SUNPHARMA.NS,SYNDIBANK.NS,TATAELXSI.NS,TATAGLOBAL.NS,TATAMOTORS.NS,TATASTEEL.NS,TECHNO.NS,TEXINFRA.NS,TIDEWATER.NS,TIIL.NS,TITAN.NS,TRIDENT.NS,UBL.NS,UNITY.NS,UPL.NS,VINATIORGA.NS,VIRINCHI.BO,WATERBASE.BO,WELSPUNIND.NS,YESBANK.NS,ZENSARTECH.NS&f=d1ohgl1vst1&e=.csv
13:54:41 --- Finished. Request count: 1. Request succeeded: 1. Request failed: 0. Duration: 1 sec.
Quotes Requested/Received/Used : 122/0/0. Quote time: .
=======================================================================================
Yahoo Finance does not provide intraday quotes for all markets. And you are using symbols from .NS market - and I checked, intraday quotes for this market is not available.
Sorry, but there is nothing I can do here.