NEPSE Lab – Portfolio in Google Sheets (Part 4)

by

 

यस भागमा हामी हाम्रो Google Sheet Portfolio System लाई अझ advanced बनाउँछौं। अघिल्लो भागहरूमा हामीले portfolio को basic structure तयार गरेका थियौं, तर Part 4 मा हामी portfolio लाई live market data सँग जोड्ने काम गर्छौं।

1. Live Market Data Import

यस चरणमा हामी अरु financial website बाट LTP (Last Traded Price) लाई Google Sheets मा automatically import गर्ने तरिका सिक्छौं।
यसका लागि Google Sheets का powerful functions प्रयोग गरिन्छ जसले गर्दा:

  • Live LTP (Last Traded Price) automatically update हुन्छ
  • Data manually update गर्नुपर्ने आवश्यकता हट्छ
  • Portfolio real-time मा track गर्न सकिन्छ

2. Automatic Portfolio Fields

जब live data import हुन्छ, त्यसपछि हामी portfolio मा रहेका महत्वपूर्ण columns लाई automatic calculation मा बदल्छौं।

यस भागमा हामी यी fields लाई automatic बनाउँछौं:

  • Symbol – Stock symbol automatic fill
  • Ext Qty – Total extended quantity calculation
  • Ext WACC – Weighted Average Cost calculation
  • LTP – Live market price
  • Change Price – Current price change calculation

यी सबै Google Sheets formulas प्रयोग गरेर automatically calculate हुने बनाइन्छ।

3. Smart Input Form System

Portfolio मा data entry सजिलो बनाउन हामी Input Form system प्रयोग गर्छौं जसले गर्दा:

  • Stock buy/sell entry सजिलो हुन्छ
  • Manual error कम हुन्छ
  • Portfolio automatically update हुन्छ

4. Professional Dashboard Creation

अन्तिम चरणमा हामी Dashboard Sheet तयार गर्छौं जसले portfolio लाई visually analyze गर्न मद्दत गर्छ।

Dashboard मा देखिने मुख्य जानकारी:

  • Total Portfolio Value
  • Total Profit / Loss
  • Individual Stock Performance
  • Portfolio Allocation
  • Live Market Value

यसले गर्दा तपाईंको Google Sheet Portfolio एकदम professional tracking tool मा परिवर्तन हुन्छ।

📊 Result:
यो Part सकिएपछि तपाईंको Google Sheet Portfolio मा Live LTP data, automatic calculations, र professional dashboard तयार भइसकेको हुन्छ।

stockprice.gs

You just cupy and pest this formula for live price update. You can set the time triggers for your wish.

function getLTP() {

var ss =SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“LTP”)

var formula =’=IMPORTHTML(P2,”Table”,1)’

ss.getRange(‘A1’).setValue(formula)

}

Symbol=UNIQUE(Data!T:T)
Sector=ARRAYFORMULA(IF(D3<>””,INDEX(Data!AJ:AJ,MATCH(D3,Data!T:T,0)),””))
Position=ARRAYFORMULA(IF(D3<>””,INDEX(Data!H:H,MATCH(D3,Data!T:T,0)),””))
Qty.=ARRAYFORMULA(IF(D3<>””,INDEX(Data!AF:AF,MATCH(D3,Data!T:T,0)),””))
WACC=IF(D3<>””,INDEX(Data!AG:AG,MATCH(D3,Data!T:T,0)),””)
Investment=IF(D3<>””,G3*H3,””)
Alloc %=IF(D3=””,””, IF(SUM($I$3:$I$29)=0, “”, I3 / SUM($I$3:$I$29)))
Ltp.=IF(D3<>””,INDEX(Data!U:U,MATCH(D3,Data!T:T,0)),””)
Chg.=IF(D3<>””,INDEX(Data!V:V,MATCH(D3,Data!T:T,0)),””)
% Chg.=IF(D3<>””,INDEX(Data!W:W,MATCH(D3,Data!T:T,0)),””)
Today’s Value=IF(D3<>””,INDEX(Data!AH:AH,MATCH(D3,Data!T:T,0)),””)
Today’s Profit=IF(D3<>””,L3*G3,””)
Current P/L=IF(D3<>””,N3-I3,””)
% Current P/L=IF(D3<>””,IFERROR(P3/I3,0),””)
Sold P/L=IF(D3<>””,SUMIF(Data!T:T,D3,Data!Q:Q),””)
Overal P/L=IF(D3<>””,P3+R3,””)
Now we are creating the Holding Sheet. If you copy and paste the formulas, make sure you follow the same cell references that I use in the sheet.

INVESTMENT SUMMARY

SummaryCurrentSoldOveralToday’s
Investment=F4-E4=SUMIF(Data!A:A,”Sell”,Data!J:J)=SUMIF(Data!A:A,”Buy”,Data!P:P)=SUMIFS(Data!P:P,Data!B:B,”>=”&TODAY(),Data!B:B,”<“&TODAY()+1,Data!A:A,”Buy”)
Market Value=sum(T3:T)=SUMIF(Data!A:A,”Sell”,Data!P:P)=sum(D5:E5)=SUMIFS(Data!P:P,Data!B:B,”>=”&TODAY(),Data!B:B,”<“&TODAY()+1,Data!A:A,”Sell”)
Profiit & Loss=sum(D5-D4)=sum(E5-E4)=sum(D6:E6)=sum(U3:U)
% PnL=D6/D4=E6/E4=F6/F4=G6/D4

Leave a Comment