Sabtu, 31 Agustus 2013

[belajar-excel] Digest Number 2505

5 New Messages

Digest #2505
1a
Re: Merubah Hitungan Vipot????? by "Hendrik Karnadi" hendrikkarnadi
1b
Fw: [belajar-excel] Merubah Hitungan Vipot????? by "hendrik karnadi" hendrikkarnadi
1c
Re: Merubah Hitungan Vipot????? by "Mr. Kid" nmkid.family@ymail.com
2a
Re: menghilangkan titik dan koma by "Sms Ticketing" sms_ticketing

Messages

Fri Aug 30, 2013 6:12 am (PDT) . Posted by:

"Hendrik Karnadi" hendrikkarnadi

Vipot tabel tidak ada, yg ada adalah PivotTable. Kalau maksudnya A_Amount-B_Amount maka grand total column harus di off dan diganti dgn calculated field. Judul field mis. Net amount.

Salam, 
HK

Sent from Samsung MobileShenly <shenly_excelmania@yahoo.co.id> wrote:Haloo master, mau tanya mengenai Vipot tabel
gimana yah cara merubah atw membut hitungan tersediri di pivot, biasanya kan hitungan yang tersedia di vipot cuma total pertambahan saja
untuk lbh jls file contoh sudah sya lampirkan

terima kasih
 
Salam
Shenly ^^*

Fri Aug 30, 2013 9:39 am (PDT) . Posted by:

"hendrik karnadi" hendrikkarnadi

Maaf. ada kesalahan teknis, ternyata PivotTable nya bersumber dari 2 sheet.
Untuk itu perlu sedikit improvisasi.

Langkah2 nya adalah sbb:
1. Arahkan penunjuk sel pada PivotTable
2. Klik Design, Grand Totals, On for Columns only
3. Klik Options, Fields, Items, & Sets, pilih Calculated Fields
4. Lakukan seperti gambar sebelah kiri, Net = -Value
5. Lakukan seperti gambar sebelah kanan, Show values as Different From, Base field Column, Base item A_Amount
Setelah itu akan muncul dua kolom "Sum of Net", yang satu kosong, yang lain adalah hasil pengurangan (A-B)
Kemudian sembunyikan kolom kosong.... selesai.

File terlampir.

Salam,
HK

----- Forwarded Message -----
From: Hendrik Karnadi <hendrikkarnadi@yahoo.com>
To: belajar-excel@yahoogroups.com
Sent: Friday, 30 August 2013, 20:12
Subject: RE: [belajar-excel] Merubah Hitungan Vipot?????


 
Vipot tabel tidak ada, yg ada adalah PivotTable. Kalau maksudnya A_Amount-B_Amount maka grand total column harus di off dan diganti dgn calculated field. Judul field mis. Net amount.

Salam, 
HK

Sent from Samsung Mobile
Shenly <shenly_excelmania@yahoo.co.id> wrote:
 
Haloo master, mau tanya mengenai Vipot tabel
gimana yah cara merubah atw membut hitungan tersediri di pivot, biasanya kan hitungan yang tersedia di vipot cuma total pertambahan saja
untuk lbh jls file contoh sudah sya lampirkan

terima kasih
 
Salam
Shenly ^^*

Fri Aug 30, 2013 6:31 pm (PDT) . Posted by:

"Mr. Kid" nmkid.family@ymail.com

Hai Shenly,

File terlampir mencoba dengan : (pilih salah satu)
1. Calculated items
2. Penataan data source

Wassalam,
Kid.

2013/8/30 Shenly <shenly_excelmania@yahoo.co.id>

> **
>
>
> Haloo master, mau tanya mengenai Vipot tabel
> gimana yah cara merubah atw membut hitungan tersediri di pivot, biasanya
> kan hitungan yang tersedia di vipot cuma total pertambahan saja
> untuk lbh jls file contoh sudah sya lampirkan
>
> terima kasih
>
> Salam
> Shenly ^^*
>
>
>

Fri Aug 30, 2013 4:39 pm (PDT) . Posted by:

"Sms Ticketing" sms_ticketing

ooooo mumtaz jazakallah khair

________________________________
From: Mr. Kid <mr.nmkid@gmail.com>
To: BeExcel <belajar-excel@yahoogroups.com>
Sent: Friday, August 30, 2013 5:33 PM
Subject: Re: [belajar-excel] menghilangkan titik dan koma


 
Data berupa angka-angka lebih dari 15 digit dapat disimpan disebuah cell dengan bertype text 15 karakter.
Cara entry : (pilih salah satu)
1. pada sebuah cell kosong, tekan tombol petik satu ( ' ) di keyboard, kemudian dilanjutkan dengan mengetik angka-angka datanya.
2. pada sebuah cell kosong, klik kanan -> pilih format cells -> tab number -> pilih Text -> tekan OK -> ketik angka-angka datanya.

3. pada sebuah cell kosong, ketik sama dengan, lalu petik dua sebanyak 2 kali ( "" ) lalu ampersand ( & ) diikuti angka-angka datanya.

Jika ada sebuah cell yang sudah terlanjur berisi data angka lebih dari 15 digit, maka sudah pasti digit terakhirnya akan diganti dengan angka 0 dan tidak dapat dikembalikan lagi selain dengan meng-entry ulang.

Wassalam,
Kid.

2013/8/30 Sms Ticketing <sms_ticketing@yahoo.com>


>masih hilang
>
>
>
>
>________________________________
> From: zainul_ulum[at]yahoo[dot]com <zainul_ulum@yahoo.com>
>To: belajar-excel@yahoogroups.com
>Sent: Friday, August 30, 2013 3:20 PM
>Subject: RE: [belajar-excel] menghilangkan titik dan koma
>
>
>

>coba ditulis
>'3307096701940001
>
>apakah angka masih hilang?
>
>From: belajar-excel@yahoogroups.com [mailto:belajar-excel@yahoogroups.com] On Behalf Of Sms Ticketing
>Sent: Friday, August 30, 2013 2:38 PM
>To: belajar-excel@yahoogroups.com
>Subject: Re: [belajar-excel] menghilangkan titik dan koma
>
>Mav be -exceller nanya lagi ni:
>saya mau tulis no KTP spt ni: tp yg muncul selalu angka 1 digit paling belakang 3307096701940001 hilang, minta tolg para master, thanks berat ats att & bantuan@
>
>Mr. Abou
>
>________________________________________
>From: Mr. Kid <mr.nmkid@gmail.com>
>To: BeExcel <belajar-excel@yahoogroups.com>
>Sent: Friday, August 30, 2013 1:17 AM
>Subject: Re: [belajar-excel] menghilangkan titik dan koma
>
>Misal data di cell A1.
>Jika cell A1 diatur formatnya menjadi general tidak ada perubahan dalam penampakan, maka cell berisi data ber-type text.
>Pada cell B1 bisa diberi formula :
>=Substitute( Substitute( Mid( A1 , 4 , 99 ) , "," , "" ) , "-" , "" )
>Jika regional setting komputer setempat adalah Indonesia, ganti seluruh karakter koma dalam formula menjadi karakter titik koma kecuali karakter koma pada bagian berbunyi ","
>
>Wassalam,
>Kid.
>
>2013/8/30 Sms Ticketing <sms_ticketing@yahoo.com>
>
>
>saya punya data dalam cell Rp.12,450,000,- tolg be-exceller minta format@ spt apa agar yg muncul
>
>12450000 (hanya angka, karena data bnyak & berulang jadi kalau delete satu persatu kemeng bin keju)
>
>alhamdulillah mr kid bantu u/ menghilangkan titik atau koma saja, sy sdh otak atik formula untuk
>
>menghilangkan Rp, titik, koma sekaligus strip dgn satu formula belum bisa2
>
>tolg para master y, plz help thanks berat w jazakumullah khair
>
>
>
>

Fri Aug 30, 2013 9:02 pm (PDT) . Posted by:

"WIRAnata Kemala" kwiranata

rekan-rekan yang berbahagia..

mohon bantuan untuk VBA nya :)

misalkan dalam satu folder explorer ada 4 .xlsx files,
pengennya langsung masing-masing file bisa langsung dipassword-protect
sekali jalan, dengan menggunakan password yang berbeda-beda (no pattern)

folder name: cabang

file name password
========================
jakarta.xlsx pass1
bogor.xlsx z99
gading.xlsx pas17
sunter.xlsx 9876
dst

masing-masing password harus di pair dengan pasangan .xlsx nya (dan dengan
mudah dapat diganti di setting), mungkin dibikin definisi di .txt terpisah?

terima kasih banyak untuk bantuannya, dan mohon petunjuk untuk belajar VBA
di weekend ini :)

--
regards, Wira 鄧又銘
on desktop FireFox
GROUP FOOTER MESSAGE
=====================================================================
Untuk memudahkan tim penyusun materi Belajar Excel yang lebih sesuai kebutuhan member, silakan ungkapkan permasalahan yang kerap ditemui dalam menggunakan Excel sehari-hari atau hal-hal yang ingin dipelajari dalam jangka dekat ini. Mohon diprioritaskan dari yang sering ditemui sampai yang ingin dipelajari.
Isi sesuai kelompoknya (fitur-fitur, formula-formula tertentu yang masih membingungkan, otomasi atau pemrograman dalam Excel [Macro - VBA], hal lainnya yang membuat Anda kesulitan dalam mempelajari Excel).
Boleh mengisi berulang kali untuk menambah uneg-uneg yang ingin diungkapkan.
Link untuk menuangkan seluruh uneg-uneg tersebut ada di :
http://tech.groups.yahoo.com/group/belajar-excel/database?method=addRecord&tbl=3
=====================================================================
Langkah kecil Anda dalam mengisi database bisa menjadi langkah pertama yang bermanfaat besar untuk kita semua.
=====================================================================

---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke:
belajar-excel@yahoogroups.com

berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran
http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe):
kirim mail kosong ke  belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
READ MORE....

[smf_addin] Digest Number 2752

4 New Messages

Digest #2752

Messages

Fri Aug 30, 2013 5:34 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I don't see how the number of rows is a problem. You just use the
RCHGetHTMLTable() over enough rows to grab what you expect to be the
largest table. Even 100 rows if you need.

And if there is no table, the RCHGetHTMLTable() area will just be filled
with blanks, so I don't see how it's a problem.

On Thu, Aug 29, 2013 at 10:34 AM, Kermit W. Prather <kermitp@tampabay.rr.com
> wrote:

>
>
> I want to download the complete table and then calculate the shares
> because the number of rows varies from stock to stock. Some stocks have no
> splits (AGNC)****
>
> so there is not table. ****
>
> Just a text message at top of page. I can retrieve with ****
>
> =smfGetTagContent("http://getsplithistory.com/"&A1,"div",0,"splits-text";)
> ****
>
> this returns****
>
> <b>International Business Machines Corp.</b> has had <b>7</b> splits.****
>
> Where the 7 tell me there has been 7 splits so there will be 7 rows in
> the table. ****
>
> Some stock will have more Walmart has 9 splits.****
>
> So I need the table to be expandable.****
>
> ** **
>
> My real problem is I can't figure out how to download the table. ****
>
>
>

Fri Aug 30, 2013 7:23 am (PDT) . Posted by:

bushpilote

Good morning Randy,
Just want to add my two cents to the Advfn data discussion.

I was able to bring FTR'S latest and historical financial data into a spreadsheet from the new Advfn site with the smf-addin. It's not as elegant and efficient as it was prior to the revamped website but it worked with FTR.

For those who are interested in testing it out here's how you do it:

1. Go to this Group files section
2. Open SmfGetTagContent
3. Download and open SmfGetTagContent-Quick-Webpage-Examination.xls
4. Paste the following url (http://www.advfn.com/common/financial/NASDAQ/frontier-communications-corp-mm-FTR/full-financials?isfull=1&bsfull=1&cffull=1) in the web page cell. Do this in all of the five sheets of the spreadsheet. Data will start to appear.
5. Make a copy of "By Table Cell" sheet in the same spreadsheet
a)Paste the url address in 4 in cell C9
b)Keep cells c10:c14 blank
c)Paste =IF($C$9="None","--",RCHGetTableCell($C$9,C$17,"<body",,,,$B18,,,"--")) in C18
d)Drag formula in C18 across columns(to 12) and rows (to 7)
f)Extending columns and formula from 12 (N17) to 90 (CN17) and rows B25 (7) to 371 (B389) or so will give you all annual financial statements for frontier communications.
g) For Quarterly data paste this url:(http://www.advfn.com/common/financial/NASDAQ/frontier-communications-corp-mm-FTR/full-financials?isfull=1&bsfull=1&cffull=1&pm=Q)in C9.
h) Be patient. It will take some time to populate the spreadsheet.

Question: What does "mm" between "copr" and "FTR" stand for?

Hope this helps.

--- In smf_addin@yahoogroups.com, "osv_jaejun&quot; <jjun0366@...> wrote:
>
>
>
> This is for you if you are in need of financial data to import into your spreadsheet.
>
> I am very grateful to Randy for his amazing add-in and I used it primarily for many years and built a lot of spreadsheets out of it.
>
> But last year, websites started to change designs to become more dynamic and used less tables which meant data couldn't be pulled. I figured that sooner than later, scraping data was going to become difficult.
>
> Seems like this situation is very close.
>
> My name is Jae Jun and I run a website (oldschoolvalue.com) with a subscription to my own data feed for my stock analyzer product. It downloads and imports all the financial data along with valuation models and the works. You can use the SMF add-in along with the spreadsheet without any issues.
>
> But here's the deal. Until I started paying for the data, I didn't realize how expensive it was.
>
> With Randy's permission, if you are interested in accessing JUST the financial statements (downloads 10 annual statements and 5 quarterly numbers) without worrying about websites screwing up, then I can create a barebones version of my spreadsheet and offer it to SMF members ONLY. Think of it as a template.
>
> There will be a subscription price associated with it.
> Discounted of course because it was thanks to Randy for giving me the idea in the first place. Cost will likely be $20/month which is peanuts compared to the time and headache you will save yourself.
>
> Below is a sample excel file of what I'm talking about. You get the full financial statements, just like you did with advfn before all these changes.
>
> http://oldschoolvalue.s3.amazonaws.com/demo/SMF-Members-Only-Sample_Stock_Analyzer_Barebones.xls
>
> If Randy doesn't like the idea of selling on his group, then I apologize and won't bring it up again. Just think that this will be a good long-term solution to the current problems.
>
> If Randy is ok with it and there is enough people who are interested, then I will try to set up everything to make it happen.
>
> Before I post any more details, I'll see what Randy and you all think.
>
>
>
> --- In smf_addin@yahoogroups.com, "Kermit W. Prather" <kermitp@> wrote:
> >
> > Folks, I think we have pretty well established that ADVFN site is not
> > working.
> >
> > I don't think we need any more posting to tell us what we already know.
> >
> > Come to think of it, we probably don't need my posting either.
> >
> > Have a nice day, everyone
> >
> > Kermit
> >
> > -----Original Message-----
> > From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
> > Of codyklein
> > Sent: Wednesday, August 28, 2013 7:26 PM
> > To: smf_addin@yahoogroups.com
> > Subject: [smf_addin] Re: Advfn data not updating in smf_addin
> >
> > The new site is disappointing IMO. Heres a peek at FTR:
> > http://www.advfn.com/common/financial/NASDAQ/frontier-communications-corp-mm
> > -FTR/full-financials?pm=Q
> >
> >
> > --- In smf_addin@yahoogroups.com, "codyklein&quot; <codyklein@> wrote:
> > >
> > > The ADVFN site isn't even working right if you try and go back to get
> > annual data starting in 2003 or so it takes you to the wrong page, it kicks
> > you back to the summary page.
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>

Fri Aug 30, 2013 5:27 pm (PDT) . Posted by:

mokwit

Does anyone know the GOOGLE Finance data element for number of
shares in float as defined by GOOG in their stock screener?

FloatShares outstanding, excluding those owned by insiders,
owned by 5%-or-more owners, or subject to SEC rule 144
(regarding restricted securities).

Fri Aug 30, 2013 9:40 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Nothing that I'm aware of...I don't think they even display it when getting
information by ticker symbol.

On Fri, Aug 30, 2013 at 5:27 PM, <pmanz@usa.net> wrote:

>
> Does anyone know the GOOGLE Finance data element for number of shares in
> float as defined by GOOG in their stock screener?
>
> Float
> Shares outstanding, excluding those owned by insiders, owned by 5%-or-more
> owners, or subject to SEC rule 144 (regarding restricted securities).
>
READ MORE....